Resolved - How to count the number of rows except hidden rows?

[cssriraman]

I have some sort of data on my excel sheet in 30 rows. Let say A1 to A30.

Here some rows were hidden. Let say A5 to A8 totally 4 rows were hidden. I just want to count the number of rows except hidden rows.

I just tried the following code.


MsgBox ActiveSheet.UsedRange.Rows.Count


This code counts the all row which are used in active sheet. The above code gives the answer 30. but my answer should be 26 not 30.

Thanks in advance!

CS.

[JustinLabenne]

How about a function to get it:
Option Explicit

Public Function COUNTVISIBLE(rng As Range) As Integer
' Counts visible cells
Application.Volatile True
Dim rcount%
Dim r As Range

rcount = 0

Set rng = Intersect(rng.Parent.UsedRange, rng)

For Each r In rng
If Not r.EntireRow.Hidden And _
Not r.EntireColumn.Hidden Then _
rcount = rcount + 1
Next r

COUNTVISIBLE = rcount
End Function

[RobDog888]

Thats nice Justin, but how about all in one line of code? :D
Option Explicit

Public Function CountMe()
CountMe = ActiveSheet.Range("A1:A30").Cells.SpecialCells(xlCellTypeVisible).Count '=26
End Function

[cssriraman]

Rob, That's great! Thanks a lot!

Thats nice Justin, but how about all in one line of code? :D
Option Explicit

Public Function CountMe()
CountMe = ActiveSheet.Range("A1:A30").Cells.SpecialCells(xlCellTypeVisible).Count '=26
End Function

Once again Thanks,

CS.

[JustinLabenne]

As long as the user doesn't have all rows from 1-30 hidden, (serious longshot) and doesn't use it like a formula in a cell, looks very nice. :thumb:

[RobDog888]

Correct. Let say A1 to A30.Thanks, but its the best there is for the range but if there is a need for an entire row then Justin has it hands down. :thumb: ;)