Create an Excel List of Hidden Rows and Columns with VBA
Question details
List rows and columns that are already hidden in an Excel worksheet.

- 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.
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.
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.
In desktop Excel, select Developer > Visual Basic. If the Developer tab is not visible, enable it in Excel's ribbon settings.
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
Return to Excel and select the worksheet containing the hidden rows and columns. Choose Developer > Macros, select ListHiddenRowsAndColumns, and click Run.
Open the Hidden List sheet. Column A contains hidden row numbers; columns B and C contain hidden column names and numbers.

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.

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.




