return row count of filtered data.

  • Thread starter Thread starter tarns
  • Start date Start date
T

tarns

I want to be able to count the amount of rows returned after i filter
some data. If the row count>1, eg atleast one row returned then i want
the script to execute some code

When i pause my macro, i can see 11 rows are selected, and those 11
rows are copied to the new sheet but the row count returned is too
large.


Code:
--------------------

Sheets("List").Select
Selection.AutoFilter Field:=7, Criteria1:=">=" & Range("I1").Value ' Filter data. (Rows returned are 11)
Set NewIssueRC = ActiveSheet.AutoFilter.Range
NewIssueRC.Copy 'Copy Filtered Data. 11 rows are selected
Count = ActiveSheet.AutoFilter.Range.Count ' DOSNT WORK. Returns 3441 rows of data.

If Count > 0 Then
Application.StatusBar = "CREATING NEW ISSUES PIVOT TABLE......."
........
........
 
You could use the worksheet subtotal function to count the number of values
in the filtered list, set the 'myRange' for the total un-filtered range
first e.g........


' the unfiltered list range extent
Set myRange = Range("A4:A1000")

' get the filtered list count
Count = Application.WorksheetFunction.Subtotal(3, myRange)

--
Cheers
Nigel



tarns said:
I want to be able to count the amount of rows returned after i filter
some data. If the row count>1, eg atleast one row returned then i want
the script to execute some code

When i pause my macro, i can see 11 rows are selected, and those 11
rows are copied to the new sheet but the row count returned is too
large.


Code:
Click to expand...
Filter data. (Rows returned are 11)
Set NewIssueRC = ActiveSheet.AutoFilter.Range
NewIssueRC.Copy 'Copy
Click to expand...
Filtered Data. 11 rows are selected
 
Thanks bro, also got it with

NewIssueCount =
ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlVisible).Count -
1
 
Back
Top