How to determine number of rows returned by a filter

[kismet]

How do I determine the number of rows returned after using an AutoFilter?

 

[xlbo]

number of rows or last row ???

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[SkipVought]

Hi,

Number of rows is

=SUBTOTAL(3,A:A)-1

assuming that in column a you ONLY have the table you are filtering with ONE heading row.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[kismet]

total number of rows/records

 

[TonyJollans]

Hi kismet,

It is displayed in the Status Bar after you apply the filter.

In VBA you can get at the visible cells in a range with the SpecialCells(xlCellTypeVisible) method, which should help.

Enjoy,
Tony

 

[TonyJollans]

Hi kismet,

To take it a stage further, using the built in range for the filter ..

Range("_FilterDatabase").SpecialCells(xlCellTypeVisible).Count

.. should give the result you want.

Enjoy,
Tony

 

[Loomah]

Tony,
You just beat me with the status bar thing. I've been playing with specialcells but can't get a result from it. I hate it when things like that happen, you think you know how to do something you think is straight forward and it won't work.

Count rows of current region of visible cells etc etc etc
:-(

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[froshie]

Tony,

The heading of my worksheet is in Row 2. The solution you provided works when the filter does return a record. However, my code isn't working when the filter returns no record.

Rows("2:2&quot.Select
Selection.AutoFilter
Selection.AutoFilter Field:=9, Criteria1:=&quot;<>Completed&quot;, Operator:=xlAnd
Selection.AutoFilter Field:=3, Criteria1:=&quot;=MGN&quot;, Operator:=xlAnd

Range(&quot;A2&quot;, Selection.End(xlDown)).Select
MGNCnt = Selection.SpecialCells(xlCellTypeVisible).Count - 1

What should I change?

 

[TonyJollans]

Hi kismet,

Instead of Selecting your own, try using either the (first column of the) built-in range ..

MGNCnt = Range("_FilterDatabase").Columns(1).SpecialCells(xlCellTypeVisible).Count - 1

.. or, slightly more obviously, the number of visible rows in the same built-in range ..

MGNCnt = Range("_FilterDatabase").SpecialCells(xlCellTypeVisible).Rows.Count - 1

Enjoy,
Tony

 

[Loomah]

Tony
Re my previous post and your last one, while trying to respond to this query I am getting incorrect results from using
SpecialCells(xlCellTypeVisible).Rows.Count

It's the same whether I use Range(&quot;_FilterDatabase&quot or Range(&quot;A2&quot.CurrentRegion as a reference to the range.

Even using Selection.Rows.Count on a manually selected non-contiguous range only seems to return the number of rows in the first selected AREA within the whole range.

Any ideas about this???

The first of the suggestions you've given works fine for me though!

Anyway, as I've done these I'll post them out of interest but neither is the most eficient way of doing this.

Sub c()
Dim lTotRows As Long
Dim lCells As Long, iCols As Integer
With Range("_FilterDatabase")
    lCells = .SpecialCells(xlCellTypeVisible).Count
    iCols = .Columns.Count
End With
lTotRows = (lCells / iCols) - 1
MsgBox "Total Filtered Rows = " & lTotRows
End Sub

Sub d()
Dim lRow As Long, lCount As Long
With Range("_FilterDatabase").SpecialCells(xlCellTypeVisible)
    For lCount = 1 To .Areas.Count
        lRow = lRow + .Areas(lCount).Rows.Count
    Next
End With
MsgBox "Total Filtered Rows = " & lRow - 1
End Sub

Lost, of London!
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[TonyJollans]

Hi Loomah,

Mea culpa! I do get the same results as you.

Two questions spring to mind ..

1. Why should the FilterDatabase Range be any different from any other? Range Properties mostly relate to the first Area.
2. What is the point of the SpecialCells(Visible) as, by definition, all the cells in each Area ARE visible?
3. Alright, three questions. Why does it seem to work 'correctly' on a single column Range?

Unfortunately, I don't really have any answers.

Enjoy,
Tony