return row count of filtered data.

[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......."
........
........

 

[Nigel]

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

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:

Filter data. (Rows returned are 11)

Set NewIssueRC = ActiveSheet.AutoFilter.Range
NewIssueRC.Copy 'Copy

Filtered Data. 11 rows are selected

 

[tarns]

Thanks bro, also got it with

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