[Excel]VBAで行の表示/非表示を切り替え

アイキャッチVBAサンプル

職場の上司から「エクセルの行を表示/非表示を切り替えるのに折りたたみ機能(アウトライン)を使ってるんだけど、作業領域が減るから画面のスクロール量が増えるんだよね。何とかならない?」と恒例の無茶振りです。勉強を兼ねて3パターンほどコードを書いてみました。

仕様

  • A列を判定列とし、「1」が入力されている行の表示/非表示を切り替える

For Next ステートメントを使用したコード

For Next使用

Sub 非表示_1()

Dim hdnR As Range
Dim Lrow As Long
'Dim stimer As Double
'Dim ftimer As Double
'    stime = Timer  
    With ActiveSheet.UsedRange
        Lrow = .Rows(.Rows.Count).Row
    End With  
    For i = 1 To Lrow
        Select Case Cells(i, 1).Value
            Case 1
                If hdnR Is Nothing Then
                    Set hdnR = Cells(i, 1)
                Else
                    Set hdnR = Union(hdnR, Cells(i, 1))
                End If
        End Select
    Next i 
    On Error GoTo errmsg  
    hdnR.EntireRow.Hidden = True
'    ftime = Timer
'    Debug.Print ftime - stime
    Exit Sub
errmsg:
    MsgBox "該当する行がありません", vbExclamation
End Sub

Sub 再表示()
    Rows.Hidden = False
End Sub

非表示の流れとしては Lrow に最下行を格納、For~Next ステートメントで1行目から最下行まで「1」が入力されているか Select Case ステートメントでチェック、入力されている行は hdnR に格納し、Unionメソッド で結合、最後に hdnR に格納された行を非表示にする、です。Unionメソッド は1行ずつ非表示にするより結合の方が処理が早そうなので採用しました(早いかどうかはわかりません)。

再表示は Rows.Hidden = False で隠れている行を全て再表示してくれます。

非表示行がない場合、エラーになるので別処理しています。コメントアウト部分は他のパターンと処理時間を比較するのに使用したコードです。コメントアウトを外してコードを実行すると、イミディエイトウィンドウに処理時間が表示されます。

For Each Next ステートメントを使用したコード

For Each Next使用

Sub 非表示_2()

Dim hdnR As Range
Dim Lrow As Long
'Dim stimer As Double
'Dim ftimer As Double
'    stime = Timer
    With ActiveSheet.UsedRange
        Lrow = .Rows(.Rows.Count).Row
    End With
    For Each r In Range("A1", Cells(Lrow, 1))
        Select Case r.Value
            Case 1           
                If hdnR Is Nothing Then
                    Set hdnR = r
                Else
                    Set hdnR = Union(hdnR, r)
                End If
        End Select
    Next r
    On Error GoTo errmsg
    hdnR.EntireRow.Hidden = True
'    ftime = Timer
'    Debug.Print ftime - stime
    Exit Sub
errmsg:
    MsgBox "該当する行がありません", vbExclamation
End Sub

流れは For~Nextステートメントと同じです。「For Each Next の方が早い」と書かれているサイトをよく見かけますし、実際その通りですが、処理の量によってはほとんど差が出ません。このコードの場合、1048576行全てを非表示にしても For~Next との差が(私の環境で)4秒程度でした。大量のデータを処理するのでなければ書きやすい方でいいのかなと思います。

SpecialCells メソッドを使用したコード

SpecialCells使用

Sub 非表示_3()

Dim hdnR As Range
Dim Lrow As Long
'Dim stimer As Double
'Dim ftimer As Double
'    stime = Timer
    With ActiveSheet.UsedRange
        Lrow = .Rows(.Rows.Count).Row
    End With
    Set hdnR = Range("A1", Cells(Lrow, 1))
    On Error GoTo errmsg
    hdnR.SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Hidden = True
'    ftime = Timer
'    Debug.Print ftime - stime
    Exit Sub
errmsg:
    MsgBox "該当する行がありません", vbExclamation
End Sub

以下のページで読んだことを思い出して作成したコードです。

これらの追加 VBA パフォーマンスの最適化を使用する

Range.SpecialCells メソッドを使用して、コードで操作するセルの数を詳しく調べます

amazonにある中華商品の説明文のような自動翻訳はともかく、SpecialCells メソッドを使えば処理が早くなるというmicrosoft公式見解。試してみるとこれが爆速。1048576行全て非表示にするのに1秒かからないではありませんか(前述の2パターンは10秒以上かかっています)。コードもシンプルで分かりやすいし、上司にはこのパターンで提出しました。

おまけ

3パターンのコードを右クリックメニューで実行できるサンプルファイルを置いておきます。必要な方はダウンロードして下さい。右クリックメニュー追加に関するコードはこちらの記事からどうぞ

シートのイベントプロシージャに実行するマクロが指定されています(デフォルトは非表示_3=SpecialCells メソッドを使用したコード)。使用したいマクロに合わせて末尾の数字を変更して下さい。

sheet2プロシージャ

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)

Dim myCB As CommandBar
Dim cmb1 As Variant
Dim cmb2 As Variant
    For Each myCB In CommandBars
        If myCB.Name = "Cell" Then
            On Error Resume Next
            myCB.Controls("行表示/非表示").Delete
            myCB.Controls("表示").Delete
            Set cmb1 = myCB.Controls.Add(before:=1, Type:=msoControlPopup)
                With cmb1
                    .Caption = "行表示/非表示"
                End With
            Set cmb2 = cmb1.Controls.Add
                With cmb2
                    .Caption = "非表示"
                    .OnAction = "非表示_3" '末尾の数字を変更して下さい
                End With
            Set cmb2 = cmb1.Controls.Add
                With cmb2
                    .Caption = "再表示"
                    .OnAction = "再表示"
                End With
        End If
    Next myCB
End Sub

上司はすごく喜んでくれましたが、アウトラインでいいんじゃね?という思いが拭い切れない今日この頃です。

最後までご高覧いただき、ありがとうございました。

タイトルとURLをコピーしました