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:
=SUBTOTAL(109,cell_range)

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.

Simply:

1. 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;
2. In any Excel workbook: Hold the Alt key and tap the F11 function key to open the VB Editor.
3. Select the Insert menu > Module command.
4. Paste the copied code into the empty white code window.
5. To run the program, close the VB Editor and switch back to Excel.
6. Select the View tab of the ribbon, then click the Macros button to the far right (or choose the View Macros command).
7. Select the FindHidden program and click the run button.

The program will prompt you to open a workbook to inspect.

Option Explicit
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 RowCounter
Dim Sheet_Counter
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

‘Error Handling
‘On Error GoTo errorhandler

‘Speed It Up
With Application
.ScreenUpdating = False
.MultiThreadedCalculation.Enabled = True
End With

‘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”

With Application.FileDialog(msoFileDialogOpen)
.Show
.Execute
End With

Set wb = ActiveWorkbook
wb.Activate

‘Step 20: Determine file format for row count
If Len(wb.Name) – InStr(1, wb.Name, “.”) = 3 Then
RowCount = 65535
Else
RowCount = 1048576
End If

‘Step 30: Loop through sheets
For Each ws In wb.Sheets

‘Select the sheet if not hidden
If ws.Visible = True Then
ws.Select
Else
GoTo Skip_This_Sheet
End If

‘Step 35: Find the last row and last column
Call Find_End_Of_Data

‘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
End If

‘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
Exit For
End If
Next Sheet_Counter

‘If no HiddenRowReport sheet, add it and name it
If Exists = False Then
wb.Activate
wb.Sheets.Add before:=Sheets(1)
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?”
End If

‘Find last row, offset and report it
wb.Sheets(“HiddenRowReport”).Select
‘Range(“A” & rowcount).End(xlUp).Select
ActiveCell.Offset(1, 0).Activate
ActiveCell = ws.Name
‘Autofit column
ActiveCell.EntireColumn.AutoFit
ActiveCell.Offset(0, 1) = RowCounter
‘Autofit column
ActiveCell.Offset(0, 1).EntireColumn.AutoFit
ActiveCell.Offset(0, 2) = HasValues
‘Autofit column
ActiveCell.Offset(0, 2).EntireColumn.AutoFit
End If

‘Go back to sheet being searched for hidden rows
ws.Select

Next RowCounter
Skip_This_Sheet:
Next ws

‘Step 60: Message if a report was created
If Report = True Then
MsgBox “There is a report of hidden rows”
wb.Sheets(“HiddenRowReport”).Select
Else
MsgBox “No Hidden Rows Were Found”
End If

Exit Sub

errorhandler:

If Err.Number <> 0 Then
MsgBox “There was a problem”
ActiveWorkbook.BuiltinDocumentProperties(“Comments”) = Err.Number & ” ” & Err.Description
End If

End Sub

Public Sub Find_End_Of_Data()
LastRow = Range(“A1”).SpecialCells(xlCellTypeLastCell).Row + 1
LastCol = Range(“A1”).SpecialCells(xlCellTypeLastCell).Column + 1
End Sub