Extracting results of Advanced Filter.

[sirbob]

Heya, folks.

When an Advanced Filter is run, the following text appears in the
Excel status bar:

"X of Y records found"

.... where X is the number of records that matched the filter criteria,
and Y is the total number of records in the filtered range.

Is there any way to extract these values programmatically? I may be
blind, but I don't see it anywhere in the relevant documentation.
I've been able to do it by capturing the status bar text and parsing
it to extract the numbers, but I'm hoping there's a more elegant way
to go about it.

Thanks,

- David Prokopetz.

 

[Norman Jones]

Hi David,

One way:

'=============>>
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim iTotal As Long
Dim iFiltered As Long

Set WB = Workbooks("MyBook.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE
Set rng = SH.Range("_FilterDatabase")

iTotal = rng.Rows.Count - 1
iFiltered = rng.Columns(1).SpecialCells(xlVisible).Count - 1

'=============>>
Public Sub Tester0()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim iTotal As Long
Dim iFiltered As Long

Set WB = ThisWorkbook
Set SH = WB.Sheets("Sheet1")
Set rng = SH.Range("_FilterDatabase")

iTotal = rng.Rows.Count - 1
iFiltered = rng.Columns(1).SpecialCells(xlVisible).Count - 1

MsgBox Prompt:=iFiltered & " rows of " _
& iTotal & " are visible", _
Buttons:=vbInformation, _
Title:="Filter Records"

End Sub
'<<=============

 

[Norman Jones]

Hi David,

A bit of a cut & paste problem!

Tryimg again:

One way:

'=============>>
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim iTotal As Long
Dim iFiltered As Long

Set WB = Workbooks("Mybook.xls")
Set SH = WB.Sheets("Sheet1")
Set rng = SH.Range("_FilterDatabase")

iTotal = rng.Rows.Count - 1
iFiltered = rng.Columns(1).SpecialCells(xlVisible).Count - 1

MsgBox Prompt:=iFiltered & " rows of " _
& iTotal & " are visible", _
Buttons:=vbInformation, _
Title:="Filter Records"
End Sub
'<<=============