Count Cells

[Pedro]

Hi
If when I apply a Filter it gives me 5 records, or 7, or whatever number of
records.
What code should I write in order to give me the number of records (rows) ,
5, 7 whatever

thanks
Pedro

 

[Ture Magnusson]

Pedro,

Will this help? Note that this procedure assumes that the first
column of the filtered range doesn't contain any blank cells.

Sub ShowNumberOfRecords()
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range.Columns(1)
MsgBox Application.WorksheetFunction.Subtotal(3, rng) - 1
End Sub

 

[Don Guillett]

Have a look in HELP index for SUBTOTAL

 

[Tom Ogilvy]

Another (works with a column where there may be missing values)

msgbox ActiveSheet.AutoFilter.Range.Columns(1) _
.SpecialCells(xlvisible).count-1