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" Else 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.