[Excel VBA] How to Check for Hidden Rows or Columns | Using SpecialCells and Areas

Introduction

When handling business data in Excel, you may sometimes fail to notice hidden rows or columns, which can cause issues during processing. Therefore, it is very convenient to include a process in your macro that checks for the existence of hidden rows or columns beforehand.

In this article, I will introduce a practical method to determine if there are hidden rows or columns in a sheet using a combination of SpecialCells(xlCellTypeVisible) and Areas.Count.

Code Example: Detecting Hidden Rows or Columns

Sub CheckHiddenCells()
    Dim visiblePart As Range

    ' Retrieve only the visible cells on the active sheet
    Set visiblePart = ActiveSheet.Cells.SpecialCells(xlCellTypeVisible)

    ' Check the number of continuous areas
    If visiblePart.Areas.Count > 1 Then
        MsgBox "There are hidden rows or columns."
    Else
        MsgBox "All cells are visible."
    End If
End Sub

Explanation of the Code

1. ActiveSheet.Cells.SpecialCells(xlCellTypeVisible)

This retrieves only the cells currently displayed on the active sheet. Rows or columns hidden by filters or manually hidden are excluded from this range.

2. Areas.Count

This property retrieves the number of continuous ranges (Areas) of the visible cells.

If hidden rows or columns exist, the single block of cells on the sheet is split into multiple areas (separated ranges), resulting in Areas.Count > 1.

3. Notification via MsgBox

The message content branches depending on whether hidden rows/columns exist or if everything is visible.

Application: Interrupting Processing

If you want to stop the macro when hidden rows or columns are detected, use the following code:

If visiblePart.Areas.Count > 1 Then
    MsgBox "Hidden rows or columns were detected. Processing aborted.", vbExclamation
    Exit Sub
End If

Scenarios Where This Is Useful

Notes and Supplements

• Works with Filters: This method detects rows hidden by AutoFilter as well.
• Sheet Protection: If the sheet is protected, there may be restrictions. If specific ranges cannot be selected or accessed, retrieval might fail.
• Blank Cells: Even empty cells are counted as part of the “visible” area if they are not hidden.

Summary

By combining SpecialCells(xlCellTypeVisible) and Areas.Count, you can understand the structure of visible cells and easily implement a check for hidden cells before executing your main logic.