Autofiltered cell value (VBA)

J

Jaro

I would like to get cell's value [like using i.e cells(x,y).value]
from the rows which are visible after autofiltering. How to get only
these visible values?
For example:
If I have 10 rows but only 2 rows matching the autofilter criteria.

For i=1 to 10
Msgbox Range.Cells(i, 1).value
Next i

This code gives me all the values - it don't considering to autofiler
results...
 
N

Norman Jones

Hi Jaro,

One way:

Sub Tester02()
Dim cell As Range
Dim Rng As Range
Dim x As Long
Const y As Long = 1

Set Rng = ActiveSheet.AutoFilter.Range
Set Rng = Rng.Offset(1).Resize(Rng.Rows.Count - 1)
Set Rng = Rng.Columns(y).SpecialCells(xlCellTypeVisible)

For Each cell In Rng
x = x + 1
If x > 10 Then Exit For
MsgBox cell.Value
Next cell

End Sub
 
Top