Count Cells

  • Thread starter Thread starter Pedro
  • Start date Start date
P

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
 
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
 
Another (works with a column where there may be missing values)

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