Counting Rows (Excel 2000 SR-1/Excel 97)

Topic

New Reply

Hi,

How do I get a count of the rows containing data on a Filtered set of data?

Sofar, I’ve only been able to get a count, average etc that includes all the data for a filtered range eg A2:A32999.

Thanx for help

Reply | Quote

Replies

KenM

This is tricky. You have to select the Visible Rows FIRST and then get the count.

Dim lHowManyRowsFiltered As Long

With Selection
With .SpecialCells(xlCellTypeVisible)
lHowManyRowsFiltered = .Rows.Count
End With
End With

HTH

Wassim

Reply | Quote

You can use the SUBTOTAL function to evaluate only the visible rows in a filtered list, e.g to get a count use =SUBTOTAL(3,A2:A32999). The first argument determines the function to be performed, 1=AVERAGE, 2=COUNT, 3 = COUNTA, 9 = SUM etc. For a full list look up SUBTOTAL in Help.

Andrew C

Reply | Quote