Counting Hidden Rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way in Excel where I can count rows and columns and get a number
that excludes any hidden rows or columns? Thanks.
 
In a subroutine/macro?

For a single area range, you can get the number of visible rows/columns with
something like:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myVisibleCell As Range
Set myRng = ActiveSheet.Range("a1:x99")

Set myVisibleCell = Nothing
On Error Resume Next
Set myVisibleCell = myRng.Cells.SpecialCells(xlCellTypeVisible).Cells(1)
On Error Resume Next

If myVisibleCell Is Nothing Then
MsgBox "0 visible rows and 0 visible columns!"
Else
MsgBox "Visible Rows: " _
& Intersect(myVisibleCell.EntireColumn, myRng) _
.Cells.SpecialCells(xlCellTypeVisible).Cells.Count _
& vbLf & _
"Visible Cols: " _
& Intersect(myVisibleCell.EntireRow, myRng) _
.Cells.SpecialCells(xlCellTypeVisible).Cells.Count
End If
End Sub
 
Back
Top