excel-determine-hidden-rows-and-columns

Excel - Determine Hidden Rows And Columns

This macro determines hidden rows and columns in a worksheet.

Excel

  • 1496
  • 543
  • 0
  • 0
Add to collection
© 2021VBAmacros.net

1 Open MS Excel.

2 Create a blank workbook.


3 Go to Developer's tab > click on "Visual Basic" button or hit Alt + F11.


4 Go to Insert tab > click on "Module" or hit M.


5 Copy the VBA code from below.

6 Paste the code in the newly created module.


7 Go to Run tab > click on "Run Sub/UserForm" or hit F5.


8 That's it!


Advertisement

Code
'==========================================
' Determine Hidden Rows And Columns
'==========================================
Sub determineHiddenRowsColumns()
    ' Set variables
    Dim i As Long
    Dim strAddress As String
    Dim strRows As String
    Dim strColumns As String
    Dim appWorksheetFunction As WorksheetFunction
    ' Set reference to the function
    Set appWorksheetFunction = Application.WorksheetFunction
    ' Determine the hidden rows
    For i = 1 To 1048576
       If Rows(i).Hidden Then
         strRows = strRows & "   " & i
       End If
    Next i
    ' Determine the hidden columns
    For i = 1 To 16384
       If Columns(i).Hidden Then
         strAddress = Columns(i).Address(False, False)
         strColumns = strColumns & "   " & Left(strAddress, appWorksheetFunction.Find(":", strAddress) - 1)
       End If
    Next i
    ' Return a message
    If strColumns <> "" Or strRows <> "" Then
       MsgBox "Row(s): " & strRows & Chr(13) & _
              "Column(s): " & strColumns
    Else
       MsgBox "There are no hidden rows or columns."
    End If
    ' Release the function
    Set appWorksheetFunction = Nothing
End Sub
Advertisement


Comments
Sort by:
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
VBAmacros.net VBAmacros.net
Code was successfully copied!
VBAmacros.net VBAmacros.net
Please sign in!
VBAmacros.net VBAmacros.net
You've already voted!