When I do an AutoFilter, the number of records found is usually displayed in
the lower left-hand corner of the window. Say the filter result is 5 of 20
records found. How can I "capture" the 5 and 20 number values in VBA? I
want to use the values elsewhere in a macro. Thanks in advance.

Option Explicit
Sub testme()

Dim TotalRows As Long
Dim VisibleRows As Long

With ActiveSheet.AutoFilter.Range.Columns(1)
'subtract the header from both
TotalRows = .Cells.Count - 1
VisibleRows = .Cells.SpecialCells(xlCellTypeVisible).Count - 1
End With

MsgBox VisibleRows & " of " & TotalRows & " records found"
End Sub

Paputxi wrote:

When I do an AutoFilter, the number of records found is usually displayed in
the lower left-hand corner of the window. Say the filter result is 5 of 20
records found. How can I "capture" the 5 and 20 number values in VBA? I
want to use the values elsewhere in a macro. Thanks in advance.

--

Dave Peterson

Dim tot as Long, vis as Long
tot = Activesheet.Autofilter.Range.Rows.count -1
vis = Activesheet.Autofilter.Range.Columns(1).Specialcel ls(xlVisible) - 1
msgbox vis & " of " & tot

--
Regards,
Tom Ogilvy


"Paputxi" wrote in message
...
When I do an AutoFilter, the number of records found is usually displayed
in
the lower left-hand corner of the window. Say the filter result is 5 of
20
records found. How can I "capture" the 5 and 20 number values in VBA? I
want to use the values elsewhere in a macro. Thanks in advance.