Travelling cell values with filter in Excel macro.
 

In Excel, say i have two columns :

COl1 Col2
A 1
B 2
C 3
A 2
D 2
C 1
A 4

I apply filter to both columns. I filter Col1 data by choosing 'A'. COl2
then filters to 1,2,4. I have written the following macro to move from the
first cell value of Col1 to the last cell of the column after the data is
filtered.
-----------------------------------------------
Sub test()
Range("A2").Activate
While ActiveCell.Value < ""
MsgBox ActiveCell.Value
ActiveCell.Offset(1, 0).Activate
Wend
End Sub
-----------------------------------------------

When the macro runs, all values under the column gets activated & displayed
in the message box. I would like to have the filtered value alone to be
shown. Do i need to change anything in the macro ?

Travelling cell values with filter in Excel macro.
 

Nagarajan,

Try it this way:

Sub test()
Dim lRow As Long
lRow = 2
While Range("A" & lRow).Value < ""
If Not Range("A" & lRow).Rows.Hidden Then
MsgBox ActiveCell.Value
End If
lRow = lRow + 1
Wend
End Sub



"Nagarajan" wrote:

In Excel, say i have two columns :

COl1 Col2
A 1
B 2
C 3
A 2
D 2
C 1
A 4

I apply filter to both columns. I filter Col1 data by choosing 'A'. COl2
then filters to 1,2,4. I have written the following macro to move from the
first cell value of Col1 to the last cell of the column after the data is
filtered.
-----------------------------------------------
Sub test()
Range("A2").Activate
While ActiveCell.Value < ""
MsgBox ActiveCell.Value
ActiveCell.Offset(1, 0).Activate
Wend
End Sub
-----------------------------------------------

When the macro runs, all values under the column gets activated & displayed
in the message box. I would like to have the filtered value alone to be
shown. Do i need to change anything in the macro ?