Listing rows after autofilter

[DavyC]

Hi

I am using the following to obtain a list of rows after a autofiler

Range("A1").Select
Selection.AutoFilter field:=1, Criteria1:="=Blue", Operator:=xlAnd
While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
If ActiveCell.EntireRow.Hidden = False Then
If r = "" Then
r = ActiveCell.Row 'To elimante a , at he start of the list
Else
r = r & ", " & ActiveCell.Row
End If
End If
Wend

Data

a
1 blue
2 red
3 blue
4 red
5 blue
6

The above code will result with 1,3,5,6 using blue as the autofilter criteria, it is getting row 6 as it is not hidden, although it has no data.

Would using a name for the data range be better, although it will be added to and deleted

Many thanks for all the help so far and no doubt for this

Thanks
DC

[Derek]

Hello DC

Perhaps you would like to try this modification to your code:

While Not IsEmpty(ActiveCell.Offset(1, 0))

This will leave off the last blank row

regards
Derek

[Ponsy Nob.]

Or this (avoids looping) :-

[A1].AutoFilter field:=1, Criteria1:="=Blue", Operator:=xlAnd
On Error Resume Next
r = Application.WorksheetFunction.Substitute(Range([A1], [A65536].End(xlUp)).SpecialCells(xlCellTypeVisible).Address(False, False), "A", "")
On Error GoTo 0