Are There Hidden Rows or Columns?

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 + ActiveSheet.UsedRange.row - 1
    If Cells(r, 1).EntireRow.Hidden = True Then
        booRow = True
        Exit For
    End If
Next r

For c = 1 To ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.row - 1
    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"
Else
    strMessage = "There are no rows or columns hidden"
End If

MsgBox strMessage, vbOKOnly, "Results"

End Sub