CODEWITHSUNDEEP
vbaexcel
Tanmoy
Tanmoy

Reputation: 815

Incorrect count of filtered rows VBA

I have an Excel ws "Monthly Data". I am trying to apply an auto filter (xlFilterLastMonth) and count the number of filtered rows.

With Sheets("Monthly Data")                     
    .Range("A1:BB" & lastrow1).AutoFilter Field:=21, Criteria1:=xlFilterLastMonth, Operator:=xlFilterDynamic

MsgBox ws2.UsedRange.SpecialCells(xlCellTypeVisible).Rows.Count

End With

Upvotes: 0

Views: 956

Answers (3)

SierraOscar
SierraOscar

Reputation: 17637

Based on J.B.s answer:

With Sheets("Monthly Data")
    .Range("A1:BB" & lastrow1).AutoFilter Field:=21, Criteria1:=xlFilterLastMonth, Operator:=xlFilterDynamic

    Set r = Intersect(.AutoFilter.Range, .Range("A:A"))
    Filtred_Rows_Count = Application.WorksheetFunction.Subtotal(103, r) - 1

    MsgBox Filtred_Rows_Count
End With

Upvotes: 0

Anastasiya-Romanova 秀
Anastasiya-Romanova 秀

Reputation: 3368

Assuming there are headers for each column, then try this

Dim Data1 As Range, Data2 As Range


Set Data1 = Sheets("Monthly Data").Range("A1:BB" & LastRow1)

Data1.AutoFilter Field:=21, Criteria1:=xlFilterLastMonth, Operator:=xlFilterDynamic

Set Data2 = Range(Cells(2, 21), Cells(LastRow1, 21)).SpecialCells(xlCellTypeVisible)

For Each cell In Data2
    i = i + 1
Next

Worksheets("Monthly Data").AutoFilterMode = False
MsgBox "The number of filtered rows is " & i

Upvotes: 0

J.B.
J.B.

Reputation: 445

Try this:

With Sheets("Monthly Data")
    .Range("A1:BB" & lastrow1).AutoFilter Field:=21, Criteria1:=xlFilterLastMonth, Operator:=xlFilterDynamic

    Set r = Intersect(ActiveSheet.AutoFilter.Range, Range("A:A"))
    Filtred_Rows_Count = Application.WorksheetFunction.Subtotal(103, r) - 1

    MsgBox Filtred_Rows_Count
End With

Upvotes: 1

Related Questions