How to check if row is hidden in Excel?
This article is talking about checking if row is hidden in a specified range or a worksheet in Excel.
Check if row is hidden in selected range with VBA code
Check if row is hidden in a worksheet with VBA code
Check if row is hidden in selected range with VBA code
Please do as follows to check if rows are hidden in a selected range and find the hidden row number.
1. Select the range you will check for hidden rows. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy below VBA code into the code window.
VBA code: check for hidden rows in selected range
Sub HiddenRowsInRange()
'Update by Extendoffice 2018/5/15
Dim I As Long
Dim xStr As String
Dim xOne, xTwo As Long
Dim xRg, xRgVsb, xRgItem As Range
On Error Resume Next
Set xRg = Range("A1:A100")
Set xRgVsb = xRg.SpecialCells(xlCellTypeVisible)
If xRg.Count <> xRgVsb.Count Then
For I = 1 To xRgVsb.Areas.Count - 1
Set xRgItem = xRgVsb.Areas.Item(I)
xOne = xRgItem.Rows(xRgItem.Rows.Count).Row
xTwo = xRgVsb.Areas.Item(I + 1).Rows(1).Row
xStr = xStr & Str(xOne + 1) & " --" & Str(xTwo - 1) & ","
Next
xStr = Left(xStr, Len(xStr) - 1)
MsgBox "Hidden rows in selected range are:" & xStr
Else
MsgBox "No rows hidden"
End If
End Sub3. Press the F5 key to run the code.
Then a Microsoft Excel dialog box pops up to show you the hidden rows as below screenshot shown.
Check if row is hidden in a worksheet with VBA code
The below VBA code can help you to check for hidden rows in a worksheet in Excel. Please do as follows.
1. Open the worksheet you need to check for hidden rows, press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy below VBA code into the code window.
VBA code: check if row is hidden in worksheet
Sub HiddenRowsInSheet()
'Update by Extendoffice 2018/5/15
Dim I As Long
Dim xStr As String
Dim xOne, xTwo As Long
Dim xRg, xRgVsb, xRgItem As Range
On Error Resume Next
Set xRg = ActiveSheet.UsedRange
Set xRgVsb = xRg.SpecialCells(xlCellTypeVisible)
Debug.Print xRgVsb.Address
If xRg.Count <> xRgVsb.Count Then
For I = 1 To xRgVsb.Areas.Count - 1
Set xRgItem = xRgVsb.Areas.Item(I)
xOne = xRgItem.Rows(xRgItem.Rows.Count).Row
xTwo = xRgVsb.Areas.Item(I + 1).Rows(1).Row
xStr = xStr & Str(xOne + 1) & " --" & Str(xTwo - 1) & ","
Next
xStr = Left(xStr, Len(xStr) - 1)
MsgBox "Hidden rows in active sheet are:" & xStr
Else
MsgBox "No rows hidden"
End If
End Sub3. Press the F5 key to run the code.
Then a Microsoft Excel dialog box pops up to show you the hidden rows in current worksheet as below screenshot shown.
Note: If now hidden rows exist in selected range or worksheet, you will get the dialog box as below screenshot shown.
Related articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
