Excel: Displaying Number Of Filtered Rows


How to count filtered rows?

Sub Displaying_Number_Filtered_Rows()
Dim rCrit As Range
Dim aCrit
Dim Rg As Long
Application.ScreenUpdating = 0
With Sheets("Plan3")
  Set rCrit = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
  aCrit = Split(Join(Application.Transpose(rCrit), Chr(1)), Chr(1))
End With
With Sheets("Org")
   Set Rg = .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Rows.Count ' <--look wrong way
  .Range("$B$1:$B$50000").AutoFilter Field:=1, Criteria1:=aCrit, Operator:=xlFilterValues
  .Range(.Cells(2, "A"), .Range("C" & Rows.Count).End(xlUp)).Copy Destination:=Sheets("Dest").Range("A" & Rows.Count).End(xlUp).Offset(1)
  
  .ShowAllData
  End With
  Call OneMacro
  Application.ScreenUpdating = 1
  If lRows <> 0 Then MsgBox "it's was filtered " & Rg & " Rows"
End Sub


This question generated 21 answers. To proceed to the answers, click here.

This thread is current as of December 11, 2013.


For more resources for Microsoft Excel