Displaying Number Of Filtered Rows

[elsg]

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

 

[elsg]

Sorry, display error

Compile Error
i try....
the object is required

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.Columns(1).SpecialCells(xlCellTypeVisible).Count '<- look
  .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

 

[Pimagine]

Very nice solution RoryA! I really wonder where you've got that information from. I tried to look this up in VBA help and VBA lists and VBA overviews, but can't find it. What is your secret ?

*removed advice to elsg, I should have tested it first*

 

[RoryA]

@elsg
There is no 'Set' in the code I posted. I also said that row needs to be after the code that does the filtering...

 

[RoryA]

@Pimagine,

I've been doing this for a very long time.

 

[elsg]

I'm sorry but this is not!


The filter was found 3 lines based on established criteria, then the MsgBox have to say we were 3 rows copied.


The filter was found 7 lines based on established criteria, then the MsgBox have to say they were 7 rows copied.

Your line of code dropped in the same situation as post #3

 

[RoryA]

Hard to comment - I haven't seen you post any code using the line as I suggested. Do you still have it before the code that actually applies the filter? (or after the ShowAllData line??)

 

[elsg]

look
show 15 rows copied

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")
   Rg = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count '<- look
  .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
   MsgBox "it's was filtered " & Rg & " Rows"
End Sub

 

[RoryA]

   With Sheets("Org")
      .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)
      Rg = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count   '<- look where I am - AFTER the filtering!!

      .ShowAllData
   End With

 

[elsg]

you are a true genius excel, thank you!


I'm trying not to count the header?

 

[RoryA]

Just subtract 1 from the count.