Row count on the Filtered data

Question

I'm using the below code to get the count of the filtered data rows in VBA, but while getting the count, it's giving the run time error showing:

"Object required".

Could some please let me know what change(s) is needed?

Set rnData = .UsedRange

With rnData
    .AutoFilter Field:=327, Criteria1:=Mid(provarr(q), 1, 2)
    .Select
    .AutoFilter Field:=328, Criteria1:=Mid(provarr(q), 3, 7)
    .Select
    .AutoFilter Field:=330, Criteria1:=Mid(provarr(q), 10, 2)
    .Select
    .AutoFilter Field:=331, Criteria1:=Mid(provarr(q), 12, 2)
    .Select

     Rowz = .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Rows.count

     ....
End With

Persnickety

Answer 1

If you try to count the number of rows in the already autofiltered range like this:

Rowz = rnData.SpecialCells(xlCellTypeVisible).Rows.Count

It will only count the number of rows in the first contiguous visible area of the autofiltered range. E.g. if the autofilter range is rows 1 through 10 and rows 3, 5, 6, 7, and 9 are filtered, four rows are visible (rows 2, 4, 8, and 10), but it would return 2 because the first contiguous visible range is rows 1 (the header row) and 2.

A more accurate alternative is this (assuming that ws contains the worksheet with the filtered data):

Rowz = ws.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1

We have to subtract 1 to remove the header row. We need to include the header row in our counted range because SpecialCells will throw an error if no cells are found, which we want to avoid.

The Cells property will give you an accurate count even if the Range has multiple Areas, unlike the Rows property. So we just take the first column of the autofilter range and count the number of visible cells.

Cahra

Thank you for your response. I'm using excel 2007. with the above code, its showing the error message as "Object doesn't support this property or method". – Persnickety

I think I just got it. I used the below code to count the rows and it worked – Persnickety

This only counts the number of rows in the first contiguous range. I'm going to update the answer to be more accurate. – Abstract

Good catch, @Tmdean. Thanks for cleaning this up. – Cahra

The 'cells' solution s what I was looking for. Thanks :-) – Stalky

Thanks for the additional info on Cells vs Rows property, been using them for years and this explains some recurring issues. – Haematozoon

Thank you so much, this just solved a head-scratcher for me. I really appreciate the explanation. – Zamir

Love the 2nd one... "Why bother figuring out the range ourselves if we can just refer to the range that AutoFilter created for itself?" Thanks. – Brainsick

Beautiful - thanks so much! – Pistole

Answer 2

Simply put this in your code:

Application.WorksheetFunction.Subtotal(3, Range("A2:A500000"))

Make sure you apply the correct range, but just keep it to ONE column

Brownie

Perfect. Solutions mentioned above may provide incorrect results in case of empty rows in table etc. This one works like a charm :) – Johathan

For those who would need to look it up, the first parameter specifies the Aggregate Function to use; 3 is "CountA", being the number of cells in a column that are not empty. – Soapstone

Answer 3

While I agree with the results given, they didn't work for me. If your Table has a name this will work:

Public Sub GetCountOfResults(WorkSheetName As String, TableName As String)
    Dim rnData As Range
    Dim rngArea As Range
    Dim lCount As Long
        Set rnData = ThisWorkbook.Worksheets(WorkSheetName).ListObjects(TableName).Range
    With rnData
        For Each rngArea In .SpecialCells(xlCellTypeVisible).Areas
            lCount = lCount + rngArea.Rows.Count
        Next
        MsgBox "Autofilter " & lCount - 1 & " records"
    End With
    Set rnData = Nothing
    lCount = Empty      
End Sub

This is modified to work with ListObjects from an original version I found here:

http://www.ozgrid.com/forum/showthread.php?t=81858

Erect

This won't work if there are more than one Areas. Simply put, a named range can have one or more Areas and be double-counting the rows. I suggest get the count of the number of areas and use the last area range, as that is the true count of all rows in the visible list. – Supinate

Answer 4

I know this an old thread, but I found out using the Subtotal method in VBA also accurately renders a count of the rows. The formula I found is in this article, and looks like this:

Application.WorksheetFunction.Subtotal(2, .Range("A2:A" & .Rows(.Rows.Count).End(xlUp).Row))

I tested it and it came out accurately every time, rendering the correct number of visible rows in column A.

Hopefully this will help some other wayfarer of the 'Net like me.

Dropsy

Correct. You have to count backwards – Supinate

subtotal method 2 is "COUNT", it just counts the number of cells containing numbers – Consuetudinary

Answer 5

Rowz = Application.WorksheetFunction.Subtotal(2, Range("A2:A" & Rows(Rows.Count).End(xlUp).Row))

Husbandry

Answer 6

I have found a way to do this that it requires 2 steps, but it works

' to copy out a filtered selection into a different sheet


number_of_dinosaurs = WorksheetFunction.Count(Worksheets("Dinosaurs").Range("A2", "A3000"))

With Worksheets("Dinosaurs")
    .AutoFilterMode = False
    With .Range("$A$4:$E$" & number_of_dinosaurs)
        .AutoFilter Field:=2, Criteria1:="*teeth*" ' change your criteria to whatever you like
        .SpecialCells(xlCellTypeVisible).Copy Destination:=Worksheets("Bad_Dinosaurs").Range("A1")
    End With
End With


' then do a normal count on the secondary sheet  

number_of_dinosaurs_that_eat_humans = WorksheetFunction.Count(Worksheets("Bad_Dinosaurs").Range("A2", "A30000"))

Semi

Answer 7

I would think that now you have the range for each of the row, you can easily manipulate that range with the offset(row, column) action? What is the point of counting the records filtered (unless you need that count in a variable)? So instead of (or as well as in the same block) write your code action to move each row to an empty hidden sheet and once all done, you can do any work you like from the transferred range data?

Curable

Answer 8

Rowz = Application.WorksheetFunction.Subtotal(2, Range("A2:A" & Rows(Rows.Count).End(xlUp).Row))

This worked for me quite well

Fineness

Answer 9

I mannaged to achive it via formula though, assuming this table: Example table

As seen in A2, the formula

=SUBTOTAL(3;[Alias])-SUBTOTAL(3;[@Alias]:C$5)+1

is propagated down, meaning the start of the second parameter of the second SUBTOTAL will be moving as pivot.

This works as Subtotal counts all the visible filtered results getting your quantity of results (the case of the first SUBTOTAL), then we subtract the remaining rows below the current one; Finally we add the offset (the "+1") which is the number of rows above our search domain (in this case its just the header).

Rill