Report Hidden Rows With An Excel VBA Macro
Many Excel users choose to hide a row of data on a worksheet for a variety of reasons. When Excel users collaborate it’s difficult to know that a row has been hidden without careful visual inspection of every row. If a hidden row contains zero values there is no issue. However, if a hidden row contains non-zero values it’s an issue to be concerned about.
Excel has a feature to report that rows may be hidden and then offer to unhide them. However the Excel feature does not tell us which sheets or which rows were hidden or unhidden. To use the Excel feature select the File Tab > Info > Check for Issues > Inspect Document command.
Excel’s Sum function does not ignore hidden rows, which is why Microsoft recommends we use the Subtotal function with the 109 function_number argument instead.
The subtotal function might read:
To help our students produce a report of hidden rows, Excel Class is pleased to share a program that will report if your workbooks have hidden rows. Realize that the speed at which this program operates is proportional to the number of sheets and the number of rows of data per sheet to examine. Even in a large workbook the program should finish in under 5 minutes. However, if the program appears to fail you can press and hold the Esc key on your keyboard to cancel execution.
- Copy the code in the courier font below into a blank new workbook, from the Option Explicit to the End Sub at the very bottom;
- In any Excel workbook: Hold the Alt key and tap the F11 function key to open the VB Editor.
- Select the Insert menu > Module command.
- Paste the copied code into the empty white code window.
- To run the program, close the VB Editor and switch back to Excel.
- Select the View tab of the ribbon, then click the Macros button to the far right (or choose the View Macros command).
- Select the FindHidden program and click the run button.
The program will prompt you to open a workbook to inspect.
Dim msg ‘msgbox prompt
Dim ws As Worksheet
Dim wb As Workbook
Dim r As Range
Dim RowCount ‘How many rows based on .xls or .xlsx
Dim HasValues As Boolean
Dim Exists As Boolean
Dim Report As Boolean
Dim LastRow As Long
Dim LastCol As Long
Public Sub FindHidden()
‘Purpose: Ask user to open a workbook to examine;
‘look at every sheet, if any row is hidden, add a report sheet
‘detailing the sheet name, row(s) hidden, and if they contain
‘non zero values
‘On Error GoTo errorhandler
‘Speed It Up
.ScreenUpdating = False
.MultiThreadedCalculation.Enabled = True
‘Step 10: Ask user to open a workbook to examine
msg = “Please choose a workbook to examine”
MsgBox prompt:=msg, Buttons:=vbExclamation + vbOKOnly, Title:=”Select a workbook”
Set wb = ActiveWorkbook
‘Step 20: Determine file format for row count
If Len(wb.Name) – InStr(1, wb.Name, “.”) = 3 Then
RowCount = 65535
RowCount = 1048576
‘Step 30: Loop through sheets
For Each ws In wb.Sheets
‘Select the sheet if not hidden
If ws.Visible = True Then
‘Step 35: Find the last row and last column
‘Step 40: On any sheet if a row is hidden report it
For RowCounter = 1 To LastRow
If Rows(RowCounter).Height = 0 Then
‘Set a report variable to report hidden row
Report = True
‘Reset hasvalues variable
HasValues = False
‘Determine if hidden row contains a non-zero value
If Application.WorksheetFunction.Count(Rows(RowCounter)) > 0 Then
HasValues = True
‘Step 50: Make a report
‘Find if HiddenRowReport sheet exists
Exists = False
For Sheet_Counter = 1 To wb.Sheets.Count
If wb.Sheets(Sheet_Counter).Name = “HiddenRowReport” Then
Exists = True
‘If no HiddenRowReport sheet, add it and name it
If Exists = False Then
wb.Sheets(1).Name = “HiddenRowReport”
wb.Sheets(“HiddenRowReport”).Range(“A1”) = “Sheet Name”
wb.Sheets(“HiddenRowReport”).Range(“B1”) = “Row”
wb.Sheets(“HiddenRowReport”).Range(“C1”) = “Contains Non-Zero Values?”
‘Find last row, offset and report it
‘Range(“A” & rowcount).End(xlUp).Select
ActiveCell = ws.Name
ActiveCell.Offset(0, 1) = RowCounter
ActiveCell.Offset(0, 2) = HasValues
‘Go back to sheet being searched for hidden rows
‘Step 60: Message if a report was created
If Report = True Then
MsgBox “There is a report of hidden rows”
MsgBox “No Hidden Rows Were Found”
If Err.Number <> 0 Then
MsgBox “There was a problem”
ActiveWorkbook.BuiltinDocumentProperties(“Comments”) = Err.Number & ” ” & Err.Description
Public Sub Find_End_Of_Data()
LastRow = Range(“A1”).SpecialCells(xlCellTypeLastCell).Row + 1
LastCol = Range(“A1”).SpecialCells(xlCellTypeLastCell).Column + 1
Thanks to VBA Class Blog here: http://vbaclass.com/an-excel-vba-macro-to-report-hidden-rows/