VBA & Macro Problems

Create an Excel List of Hidden Rows and Columns with VBA

John WilsonJohn Wilson Nov 13, 2026 868 views

Question details

List rows and columns that are already hidden in an Excel worksheet.

Create an Excel List of Hidden Rows and Columns with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants a VBA macro to identify hidden rows and columns and report them on a separate worksheet.
Observed behavior
The desired output is a list of hidden row numbers and column names without changing their hidden status.
Before you start

Open the workbook in desktop Excel and create a worksheet named Hidden List. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you want to keep the macro.

Solution 1Recommended

Run a VBA macro to list hidden rows and columns

Use this macro to scan the active worksheet's used range and write the results to the Hidden List worksheet.

The macro checks rows and columns within the source worksheet's used range. Hidden rows or columns entirely outside that range are not included.

The macro does not hide or unhide anything. It clears existing cell contents on the Hidden List sheet before writing the new results.

1
Open the VBA editor

In desktop Excel, select Developer > Visual Basic. If the Developer tab is not visible, enable it in Excel's ribbon settings.

2
Insert the macro

In the VBA editor, select Insert > Module and paste the following code: Sub ListHiddenRowsAndColumns() Dim src As Worksheet, output As Worksheet Dim r As Range, c As Range Dim rowOut As Long, colOut As Long Set src = ActiveSheet On Error Resume Next Set output = src.Parent.Worksheets("Hidden List") On Error GoTo 0 If output Is Nothing Then MsgBox "Create a worksheet named Hidden List first." Exit Sub End If If src Is output Then MsgBox "Select the worksheet you want to scan first." Exit Sub End If output.Cells.ClearContents output.Range("A1").Value = "Hidden row number" output.Range("B1").Value = "Hidden column name" output.Range("C1").Value = "Hidden column number" rowOut = 2 colOut = 2 For Each r In src.UsedRange.Rows If r.EntireRow.Hidden Then output.Cells(rowOut, 1).Value = r.Row rowOut = rowOut + 1 End If Next r For Each c In src.UsedRange.Columns If c.EntireColumn.Hidden Then output.Cells(colOut, 2).Value = Split(src.Cells(1, c.Column).Address(True, False), "$")(0) output.Cells(colOut, 3).Value = c.Column colOut = colOut + 1 End If Next c MsgBox "The hidden row and column list is ready on Hidden List." End Sub

3
Run the macro

Return to Excel and select the worksheet containing the hidden rows and columns. Choose Developer > Macros, select ListHiddenRowsAndColumns, and click Run.

4
Review the list

Open the Hidden List sheet. Column A contains hidden row numbers; columns B and C contain hidden column names and numbers.

Run a VBA macro to list hidden rows and columns
Filtered rows may be included: The macro checks whether each row is hidden, so rows hidden by a filter can also appear in the list.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files to review rows and columns and transition between office suites with ease. The Excel VBA macro described above is intended for Microsoft Excel.

Review spreadsheet rows and columns in WPS SpreadsheetsOpen and edit compatible Microsoft Excel XLS and XLSX filesUse a familiar interface for everyday spreadsheet workGet a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Will the macro unhide any rows or columns?

No. It reads their hidden status and writes the results to Hidden List without changing the source worksheet.

Why is a hidden row or column missing from the list?

The macro scans only the source worksheet's used range. A hidden row or column entirely outside that range will not be reported.

Does the macro include rows hidden by a filter?

Yes. Rows hidden by a filter can be reported because the macro checks the row's Hidden property rather than why it is hidden.

What happens if I run the macro again?

It clears the existing cell contents on Hidden List and writes a fresh report for the currently selected worksheet.