Autofilter returns

[Nath]

Hi,

I have an autofilter being set up in VBA and wish to get
the row numbers of the values that are returned once the
criteria are selected.

Can anyone advise on how to do this?

Regards,

Nathan.

 

[Don Guillett]

Why? There may be an easier way to do what you want such as copy visible
cells only.
Have a look in vba HELP for SPECIALCELLS.

 

[Debra Dalgleish]

You can use SpecialCells(xlVisible) to work with the filtered rows:

Sub GetRows()
Dim rng As Range
Dim rng2 As Range
Dim c As Range

If Not ActiveSheet.AutoFilterMode Then
MsgBox "AutoFilter not active"
Exit Sub
End If

Set rng = ActiveSheet.AutoFilter.Range
Set rng2 = rng.Columns(1).SpecialCells(xlVisible)

If rng2.Count - 1 = 0 Then
MsgBox "No records found"
Else
For Each c In rng2
Debug.Print c.Row
Next c
End If

End Sub

 

[Tom Ogilvy]

Dim rng as Range
Dim rng1 as Range
Dim icnt as Long
Dim sStr as String
set rng = Activesheet.Autofilter.Range.columns(1).Cells
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
On Error Resume Next
set rng1 = rng.SpecialCells(xlVisible)
On Error goto 0
sStr = ""
if not rng1 is nothing then
icnt = 0
for each cell in rng1
icnt = icnt + 1
sStr = cell.Row & iif(icnt mode 10,", ",vbNewline)
Next
msgbox sStr
Else
msgbox "No visible rows"
End if