EXCELマクロで、ある列の値が0の場合、非表示／表示ボタンで該当列＆行を同時に非表示に切替したい。

Question

先日EXCELの質問をさせて頂きました。
https://oshiete.goo.ne.jp/qa/8944333.html
EXCELマクロで、ある列の値が0の場合、非表示／表示ボタンで該当列を非表示切り替えしたいんです。
行ではできましたが、列ではできませんでした。またボタンのワンクリックで同時に表示切り替えできれば最高です。

ちなみに行は物件名、列は業者名で入出金のあった物件以外は非表示、使用しなかった業者以外は非表示にしたいのです。
つまり業者別計で0あるいは、物件別計で0であった行あるいは列は非表示にしたいのです。
逆に考えれば計が0以外である行または列は表示でもよいのかもしれません。

何卒諸先輩方のお知恵を拝借したく存じます。宜しくお願いいたします。

tom04 · Accepted Answer

たびたびごめんなさい。

前回のコードではセルの表示形式は「数値」ではなく「通貨」になってしまいますね。
見た目は同じのようですが、どうしても「数値」で桁区切りにしたい場合は
あらかじめ設定してある表示形式を使った方が良いみたいなので↓のコードに変更してみてください。

Private Sub CommandButton1_Click()
Dim c As Range, myFlg As Boolean
 Dim myFirst As Range, myFound As Range, myRng As Range
 Dim myArea1 As Range, myArea2 As Range
 Dim str1 As String, str2 As String '//☆新たに追加
 Set myArea1 = Range("BM3:BM749")
 Set myArea2 = Range("E750:ML750")
 Application.ScreenUpdating = False
 str1 = myArea1(1).NumberFormatLocal    '//☆
 str2 = myArea2(1).NumberFormatLocal    '//☆
 myArea1.NumberFormatLocal = "G/標準"   '//★追加
 myArea2.NumberFormatLocal = "G/標準"   '//★追加
 For Each c In myArea1
 If c.EntireRow.Hidden = True Then
 myFlg = True
 Exit For
 End If
 Next c
 If myFlg = True Then
 ActiveSheet.Rows.Hidden = False
 Else
 Set myFound = myArea1.Find(what:=0, LookIn:=xlValues, lookat:=xlWhole)
 If Not myFound Is Nothing Then
 Set myFirst = myFound
 Set myRng = myFound
 Do
 Set myFound = myArea1.FindNext(after:=myFound)
 If myFound.Address = myFirst.Address Then Exit Do
 Set myRng = Union(myRng, myFound)
 Loop
 myRng.EntireRow.Hidden = True
 End If
 End If

 myFlg = False
 For Each c In myArea2
 If c.EntireColumn.Hidden = True Then
 myFlg = True
 Exit For
 End If
 Next c
 If myFlg = True Then
 ActiveSheet.Columns.Hidden = False
 Else
 Set myFound = myArea2.Find(what:=0, LookIn:=xlValues, lookat:=xlWhole)
 If Not myFound Is Nothing Then
 Set myFirst = myFound
 Set myRng = myFound
 Do
 Set myFound = myArea2.FindNext(after:=myFound)
 If myFound.Address = myFirst.Address Then Exit Do
 Set myRng = Union(myRng, myFound)
 Loop
 myRng.EntireColumn.Hidden = True
 End If
 End If
    myArea1.NumberFormatLocal = str1    '//☆
    myArea2.NumberFormatLocal = str2    '//☆
 Application.ScreenUpdating = True
End Sub

何度も失礼しました。m(_ _)m

tom04 · Answer

はぁ～～～！
そういうコトだったのですね。

それでは一旦セルの表示形式を「標準」にしてマクロを実行　→　セルの表示形式をもとに戻す
といった感じではどうでしょうか？

もう一度コードを載せてみます。（★の行が追加です）

Private Sub CommandButton1_Click()
Dim c As Range, myFlg As Boolean
 Dim myFirst As Range, myFound As Range, myRng As Range
 Dim myArea1 As Range, myArea2 As Range
 Set myArea1 = Range("BM3:BM749")
 Set myArea2 = Range("E750:ML750")
 Application.ScreenUpdating = False
 myArea1.NumberFormatLocal = "G/標準"   '//★追加
 myArea2.NumberFormatLocal = "G/標準"   '//★追加
 For Each c In myArea1
 If c.EntireRow.Hidden = True Then
 myFlg = True
 Exit For
 End If
 Next c
 If myFlg = True Then
 ActiveSheet.Rows.Hidden = False
 Else
 Set myFound = myArea1.Find(what:=0, LookIn:=xlValues, lookat:=xlWhole)
 If Not myFound Is Nothing Then
 Set myFirst = myFound
 Set myRng = myFound
 Do
 Set myFound = myArea1.FindNext(after:=myFound)
 If myFound.Address = myFirst.Address Then Exit Do
 Set myRng = Union(myRng, myFound)
 Loop
 myRng.EntireRow.Hidden = True
 End If
 End If

 myFlg = False
 For Each c In myArea2
 If c.EntireColumn.Hidden = True Then
 myFlg = True
 Exit For
 End If
 Next c
 If myFlg = True Then
 ActiveSheet.Columns.Hidden = False
 Else
 Set myFound = myArea2.Find(what:=0, LookIn:=xlValues, lookat:=xlWhole)
 If Not myFound Is Nothing Then
 Set myFirst = myFound
 Set myRng = myFound
 Do
 Set myFound = myArea2.FindNext(after:=myFound)
 If myFound.Address = myFirst.Address Then Exit Do
 Set myRng = Union(myRng, myFound)
 Loop
 myRng.EntireColumn.Hidden = True
 End If
 End If
    myArea1.NumberFormatLocal = "#,##0" '//★追加
    myArea2.NumberFormatLocal = "#,##0" '//★追加
 Application.ScreenUpdating = True
End Sub

※　セルの表示形式で小数点以下は表示させないようにしていますが、
実際のデータに合わせて、小数点以下第1位まで表示させたい場合は

＞.NumberFormatLocal = "#,##0.0"
といった感じで適宜応用してみてください。m(_ _)m

tom04 · Answer

続けてお邪魔します。
なかなかご希望通りの動きにならないようで・・・

＞当方はなんとExcel2011を使用しており
Mac版のExcelなのですね？
Mac版は手元にないので検証はできませんが、おそらくコードそのものは問題ないと思います。
前回のコードでは↓の画像（小さくて判り難いと思いますが、見た目だけで！）
数値が入っているのはBM列と750行目だけで、
黄色のセルの部分に「0」が入っています。

コマンドボタンをクリックするたびに、黄色の列・行が表示/非表示になります。

前回のコードと同じ内容ですが、少し手を加えてみました。

Private Sub CommandButton1_Click()
 Dim c As Range, myFlg As Boolean
 Dim myFirst As Range, myFound As Range, myRng As Range
 Dim myArea1 As Range, myArea2 As Range
 Set myArea1 = Range("BM3:BM749")
 Set myArea2 = Range("E750:ML750")
 Application.ScreenUpdating = False
    For Each c In myArea1
    If c.EntireRow.Hidden = True Then
        myFlg = True
        Exit For
        End If
    Next c
    If myFlg = True Then
        ActiveSheet.Rows.Hidden = False
    Else
        Set myFound = myArea1.Find(what:=0, LookIn:=xlValues, lookat:=xlWhole)
            If Not myFound Is Nothing Then
                Set myFirst = myFound
                Set myRng = myFound
            Do
                Set myFound = myArea1.FindNext(after:=myFound)
                If myFound.Address = myFirst.Address Then Exit Do
                Set myRng = Union(myRng, myFound)
            Loop
            myRng.EntireRow.Hidden = True
            End If
    End If
 
    myFlg = False
    For Each c In myArea2
        If c.EntireColumn.Hidden = True Then
            myFlg = True
            Exit For
        End If
    Next c
    If myFlg = True Then
        ActiveSheet.Columns.Hidden = False
    Else
        Set myFound = myArea2.Find(what:=0, LookIn:=xlValues, lookat:=xlWhole)
            If Not myFound Is Nothing Then
            Set myFirst = myFound
            Set myRng = myFound
        Do
            Set myFound = myArea2.FindNext(after:=myFound)
            If myFound.Address = myFirst.Address Then Exit Do
            Set myRng = Union(myRng, myFound)
        Loop
        myRng.EntireColumn.Hidden = True
    End If
    End If
    Application.ScreenUpdating = True
End Sub

※　今回は上記コードで検証してみました。
こちらではちゃんと動くみたいです。m(_ _)m

tom04 · Answer

No.1です。

＞具体的にはBM3:BM749（縦列）の集計値列、そしてE750:ML750（横列）の集計値行でどちらか１つでも0があった該当列または行に関して非表示としたいのです。

はぁ～～～！そういうコトだったのですね。
コマンドボタンを押下するたびに、
BM3～BM749セル内のが「0」の行とE750～ML750セル内の「0」の列を
コマンドボタンを押下するたびに　非表示/再表示　となれば良い訳ですね。
↓のコードに変更してみてください。

Private Sub CommandButton1_Click()
    Dim i As Long, j As Long, myFlg As Boolean
    Dim FirstCell As Range, FoundCell As Range, myArea As Range
    Dim myRng1 As Range, myRng2 As Range
    Set myRng1 = Range("BM3:BM749")
    Set myRng2 = Range("E750:ML750")
    Application.ScreenUpdating = False
    For i = 3 To 749    '//3行目～749行目まで
        If Rows(i).Hidden = True Then
            myFlg = True
            Exit For
        End If
    Next i
       If myFlg = False Then
        Set FoundCell = myRng1.Find(what:=0, LookIn:=xlValues, lookat:=xlWhole)
            If Not FoundCell Is Nothing Then
                Set FirstCell = FoundCell
                Set myArea = FoundCell
                Do
                    Set FoundCell = myRng1.FindNext(after:=FoundCell)
                    If FoundCell.Address = FirstCell.Address Then Exit Do
                    Set myArea = Union(myArea, FoundCell)
                Loop
                myArea.EntireRow.Hidden = True
            End If
    Else
        ActiveSheet.Rows.Hidden = False
    End If
    myFlg = False
    For j = 5 To 350    '//E列～ML列まで
        If Columns(j).Hidden = True Then
            myFlg = True
            Exit For
        End If
    Next j
        If myFlg = False Then
            Set FoundCell = myRng2.Find(what:=0, LookIn:=xlValues, lookat:=xlWhole)
                If Not FoundCell Is Nothing Then
                    Set FirstCell = FoundCell
                    Set myArea = FoundCell
                Do
                    Set FoundCell = myRng2.FindNext(after:=FoundCell)
                    If FoundCell.Address = FirstCell.Address Then Exit Do
                    Set myArea = Union(myArea, FoundCell)
                Loop
                myArea.EntireColumn.Hidden = True
            End If
        Else
            Columns.Hidden = False
        End If
    Application.ScreenUpdating = True
End Sub

※　じっくり考えればもっと簡単になるかもしれませんが、
とりあえずはこの程度で・・・m(_ _)m

tom04 · Answer

こんばんは！

前回はオートフィルタで処理していましたが、列方向に関してのオートフィルタは使えませんので
ループさせる方法です。
今回は特定の列を指定せず
A列～1行目のデータがある最終列までで、その列に一つでも「0」があればその列を非表示としています。

Private Sub CommandButton1_Click()
    Dim j As Long, lastCol As Long, c As Range, myFlg As Boolean
        lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
        Application.ScreenUpdating = False
        For j = 1 To lastCol
            If Columns(j).Hidden = True Then
                myFlg = True
                Exit For
            End If
        Next j
        If myFlg = True Then
            Columns.Hidden = False
        Else
            For j = 1 To lastCol
                Set c = Columns(j).Find(what:=0, LookIn:=xlValues, lookat:=xlWhole)
                    If Not c Is Nothing Then
                        Columns(j).Hidden = True
                    End If
            Next j
        End If
        Application.ScreenUpdating = True
End Sub

※　もっとスマートに出来るかもしれませんが
とりあえずはこの程度で・・・m(_ _)m

EXCELマクロで、ある列の値が0の場合、非表示／表示ボタンで該当列＆行を同時に非表示に切替したい。

たびたびごめんなさい。

はぁ～～～！

続けてお邪魔します。

No.1です。

こんばんは！

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング