How to "Capture" Records Found Number During AutoFilter
 

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.

How to "Capture" Records Found Number During AutoFilter
 

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

How to "Capture" Records Found Number During AutoFilter
 

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.