Are There Hidden Rows or Columns?

A macro answers the question

I was working on a project for a client and receiving multiple files. Some of the sheets had hidden rows or columns. I realised there is no easy way to find out if a sheet has hidden rows or columns, so I wrote a macro.

The macro is listed below. It goes through all the used rows and columns.

If it finds a hidden row or column it sets a flag (boolean variable) to True.

I then check the two boolean variables to determine the correct message to display.

Its a bit clunky, but it works.

Check out this blog post if you are new to macros – it explains how to use them.

Sub CheckForHidden()
'this macro lets you know if there are hidden rows
' and/or columns on the current sheet
Dim r, c
Dim booRow As Boolean, booCol As Boolean
Dim strMessage As String
For r = 1 To ActiveSheet.UsedRange.Rows.Count
    If Cells(r, 1).EntireRow.Hidden = True Then
        booRow = True
        Exit For
    End If
Next r
For c = 1 To ActiveSheet.UsedRange.Columns.Count
    If Cells(1, c).EntireColumn.Hidden = True Then
        booCol = True
        Exit For
    End If
Next c
If booRow And booCol Then
    strMessage = "There are rows and columns hidden"
ElseIf booRow Then
    strMessage = "There are rows hidden"
ElseIf booCol Then
    strMessage = "There are columns hidden"
    strMessage = "There are no rows or columns hidden"
End If
MsgBox strMessage, vbOKOnly, "Results"
End Sub





Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *