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.

Please Login or Register  to view this content.

Count = ActiveSheet.AutoFilter.ROW.Count ' This returns the value empty for some reason

[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



"tarns" <[email protected]> wrote in
message news:[email protected]...
>
> 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......."
> ........
> ........
>
> --------------------
>
>
> --
> tarns
> ------------------------------------------------------------------------
> tarns's Profile:
http://www.excelforum.com/member.php...o&userid=32291
> View this thread: http://www.excelforum.com/showthread...hreadid=520955
>

[tarns]

Thanks bro, also got it with

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