VBAで非表示の行や列を見つける方法｜ Excel作業をVBAで効率化

非表示の行や列を探すには

Excelで大量のデータを扱っていると、「どこかに非表示の行や列があるはずだけど見つからない…」という経験はありませんか？

Excelの非表示機能は便利ではありますが、見た目では非表示になっていることが分かりにくいため探すのが結構大変です。手作業で1行ずつ確認するのは非効率ですし、見落としのリスクもあります。

そこで、ここではVBAを使って非表示になっている行や列を見つける方法を紹介します。

行と列の非表示はコードとしては大体同じですが、実際に使う場合は「列だけ」もしくは「行だけ」の場合が多いため、それぞれを分けて紹介します。

非表示を判定する基本：Hiddenプロパティ

VBAで行や列の非表示状態を確認するには、Hiddenプロパティを使います。

Rows(行番号).Hidden = True → 非表示
Rows(行番号).Hidden = False → 表示中
Columns(列番号).Hidden = True → 非表示
Columns(列番号).Hidden = False → 表示中

このプロパティを使ってシート全体をチェックすることで、非表示箇所を特定できます。

非表示の行を探す方法

非表示の行を探すには、「Rows(行位置).Hidden」プロパティの値を確認します。HiddenプロパティがTrueの場合は非表示、Falseの場合が表示と判定します。

以下の関数はアクティブシートのセル編集範囲の先頭行目から最終行までで、非表示の行があればイミディエイトウィンドウに出力します。

UsedRangeオブジェクトを使って検索範囲を編集セルしている箇所に限定しています。単純に1行目から1048576行目まで検索すると性能が良いPCでも数秒程度は掛かりますが、この方法は編集セル範囲に限定されることによりループ回数がその行数分しか行わないため高速に終了します。

Sub FindHiddenRowUsedRange()
    Dim act         As Worksheet    '// アクティブシート
    Dim iRowStart   As Long         '// 先頭行
    Dim iRowEnd     As Long         '// 最終行
    Dim iRowCount   As Long         '// 行数
    Dim i           As Long         '// ループカウンタ
    
    '// アクティブシートを変数として設定（"ActiveSheet"って書いてもいいけど省略して書くため用）
    Set act = ActiveSheet
    
    '// 編集セル範囲の先頭行を取得
    iRowStart = act.UsedRange.Row
    '// 編集セル範囲の行数を取得
    iRowCount = act.UsedRange.Rows.Count
    '// 編集セル範囲の最終行を取得
    iRowEnd = iRowCount + iRowStart - 1
    
    '// 編集セル範囲の先頭行から最終行までループ
    For i = iRowStart To iRowEnd
        '// 現在ループ行が非表示の場合
        If act.Rows(i).Hidden = True Then
            '// イミディエイトウィンドウに出力
            Debug.Print act.Rows(i).Row
        End If
    Next
End Sub

Sub FindHiddenRowUsedRange()

Dim act As Worksheet '// アクティブシート

Dim iRowStart As Long '// 先頭行

Dim iRowEnd As Long '// 最終行

Dim iRowCount As Long '// 行数

Dim i As Long '// ループカウンタ

'// アクティブシートを変数として設定（"ActiveSheet"って書いてもいいけど省略して書くため用）

Set act = ActiveSheet

'// 編集セル範囲の先頭行を取得

iRowStart = act.UsedRange.Row

'// 編集セル範囲の行数を取得

iRowCount = act.UsedRange.Rows.Count

'// 編集セル範囲の最終行を取得

iRowEnd = iRowCount + iRowStart - 1

'// 編集セル範囲の先頭行から最終行までループ

For i = iRowStart To iRowEnd

'// 現在ループ行が非表示の場合

If act.Rows(i).Hidden = True Then

'// イミディエイトウィンドウに出力

Debug.Print act.Rows(i).Row

End If

End Sub

非表示の列を探す方法

非表示の列を探すには、「Columns(列位置).Hidden」プロパティの値を確認します。Hiddenプロパティは行の場合と同様で、Trueの場合は非表示、Falseの場合が表示と判定します。

以下の関数はアクティブシートの１列目から一番右の列までで、非表示の列があればイミディエイトウィンドウに出力します。

上の行の場合と異なりUsedRangeは使っていませんが、Excelの列数は少ないためそこまで速度低下はないことと、列の場合はUsedRangeを使うと範囲外の非表示列を検出できない問題があるため１列目から最終列まで検索する方法を採用しています。

関数が２つありますが、２つ目の関数は列の位置を英字で出力したい場合のための、列数字→英字の変換関数です。変換が不要であればコメントアウトしている数字での出力でOKです。

Sub FindHiddenColumn()
    Dim act         As Worksheet    '// アクティブシート
    Dim iEnd        As Long         '// 最終列
    Dim i           As Long         '// ループカウンタ
    
    '// アクティブシートを変数として設定（"ActiveSheet"って書いてもいいけど省略して書くため用）
    Set act = ActiveSheet
    
    '// シートの最終列を取得
    iEnd = act.Columns.Count
    
    '// 編集セル範囲の先頭列から最終列までループ
    For i = 1 To iEnd
        '// 現在ループ列が非表示の場合
        If act.Columns(i).Hidden = True Then
            '// イミディエイトウィンドウに出力
            '// 列を数字で出す場合
'            Debug.Print act.Columns(i).Column
            '// 列名を英字で出す場合
            Debug.Print GetColumnAlpha(act, act.Columns(i).Column)
        End If
    Next
End Sub

'// 列の数字を英字で返す
Function GetColumnAlpha(a_sht As Worksheet, a_iColumn As Long)
    Dim sAddress    As String   '// セル座標
    Dim sColumn     As String   '// 英字列
    
    '// 対象シートの対象列の1行目のセル位置を取得（"A1"形式）
    sAddress = a_sht.Cells(1, a_iColumn).Address(False, False)
    
    '// セル座標から列のみを取得（"A1"から数字を除いた"A"を取得）
    sColumn = Left(sAddress, Len(sAddress) - 1)
    
    GetColumnAlpha = sColumn
    
End Function

Sub FindHiddenColumn()

Dim act As Worksheet '// アクティブシート

Dim iEnd As Long '// 最終列

Dim i As Long '// ループカウンタ

'// アクティブシートを変数として設定（"ActiveSheet"って書いてもいいけど省略して書くため用）

Set act = ActiveSheet

'// シートの最終列を取得

iEnd = act.Columns.Count

'// 編集セル範囲の先頭列から最終列までループ

For i = 1 To iEnd

'// 現在ループ列が非表示の場合

If act.Columns(i).Hidden = True Then

'// イミディエイトウィンドウに出力

'// 列を数字で出す場合

' Debug.Print act.Columns(i).Column

'// 列名を英字で出す場合

Debug.Print GetColumnAlpha(act, act.Columns(i).Column)

End If

End Sub

'// 列の数字を英字で返す

Function GetColumnAlpha(a_sht As Worksheet, a_iColumn As Long)

Dim sAddress As String '// セル座標

Dim sColumn As String '// 英字列

'// 対象シートの対象列の1行目のセル位置を取得（"A1"形式）

sAddress = a_sht.Cells(1, a_iColumn).Address(False, False)

'// セル座標から列のみを取得（"A1"から数字を除いた"A"を取得）

sColumn = Left(sAddress, Len(sAddress) - 1)

GetColumnAlpha = sColumn

End Function

非表示の行・列を一括で再表示する

検出した非表示箇所をすべて表示状態に戻したい場合は、以下のようにHidden = Falseを設定します。

Sub ShowAllRowsAndColumns()
    With ActiveSheet
        .Rows.Hidden = False
        .Columns.Hidden = False
    End With
    MsgBox "すべての非表示行・列を表示しました", vbInformation
End Sub

Sub ShowAllRowsAndColumns()

With ActiveSheet

.Rows.Hidden = False

.Columns.Hidden = False

End With

MsgBox "すべての非表示行・列を表示しました", vbInformation

End Sub

非表示の行や列を探すには

非表示を判定する基本：Hiddenプロパティ

非表示の行を探す方法

非表示の列を探す方法

非表示の行・列を一括で再表示する

関連記事