CODEWITHSUNDEEP
excelvba
user2300403
user2300403

Reputation: 309

Row count on the Filtered data

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

Upvotes: 26

Views: 243726

Answers (9)

Vichaoss
Vichaoss

Reputation: 1

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).

Upvotes: 0

mahdi machani
mahdi machani

Reputation: 21

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

Upvotes: 2

Sudhanshu Soni
Sudhanshu Soni

Reputation: 1

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

This worked for me quite well

Upvotes: 0

warsong
warsong

Reputation: 1038

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"))

Upvotes: 1

Frank
Frank

Reputation: 281

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

Upvotes: 28

Joe Stellato
Joe Stellato

Reputation: 568

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

Upvotes: 4

Josh
Josh

Reputation: 149

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.

Upvotes: 2

Jon Crowell
Jon Crowell

Reputation: 22338

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.

Upvotes: 74

Marc Quattrini
Marc Quattrini

Reputation: 19

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?

Upvotes: 0

Related Questions