Displaying Number Of Filtered Rows

elsg

Active Member
Joined
Mar 16, 2013
Messages
295
How to count filtered rows?
Code:
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Shouldn't it be more like

Code:
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.SpecialCells(xlCellTypeVisible).Rows.Count ' <--No 'Set'


   .ShowAllData
 
Upvote 0
Thanks for responding, there is something wrong.

this line
Code:
. Range ("$ B $ 1: $ B $ 50,000"). AutoFilter Field: = 1, Criteria1: = aCrit, Operator: = xlFilterValues
the result is 7

wheels after the code the MsgBox shows me 15

I need to count only what is being filtered
 
Upvote 0
you get 15 records on your Dest sheet?
yeah
I'll try to explain...
in tab "Org"
NFTipoNome
1000050Maria
2000060Pedro
30000140Joao
40000200Carlos
70000300Wagner
80000123Cesar
9000060Cesar
12345660Vasco
456789141Manoel
50000141Cido
6000060Marcos
60001141Marcos
60002502Marcos
60003503Marcos

<tbody>
</tbody>

My filter must filter criteria (60, 141).


so at this moment I have 7 rows to copy
NFTipoNome
2000060Pedro
9000060Cesar
12345660Vasco
456789141Manoel
50000141Cido
6000060Marcos
60001141Marcos

<tbody>
</tbody>
 
Upvote 0
I understand you would like to solve this with a very smart macro, but sometimes simple solutions are better. Why don't you put in cell D1 the formula =subtotal(9, range) where "range" includes all rows of your table? Then enter a 1 at the right of each name and filter .....
 
Upvote 0
First you need to get the number after you filter, not before, and you need to count the cells in one column, not count rows (which doesn't work with multiple areas):
Code:
Rg = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count
 
Upvote 0

Forum statistics

Threads
1,214,516
Messages
6,119,980
Members
448,934
Latest member
audette89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top