Navigating a filtered list

[Hafeez Esmail]

I have a tonne of data with 4 key columns;
Section, Code, Date and Time
I sort the data (Code, Date, Time) and then filter it so
I'm left with one Section.

Since the first Row is always blank (and I need it to be),
how do I do the following:

Select the SECOND visible Cell in Column A?
(the first being a blank row)
Select the NEXT visible Cell in Colun A?

Let's say I want to keep the Second visible cell in Column
A as the selected cell. How do I do the following?
Get data in Column D (same row as the selected cell)?
Get data in Column D (next visible row from selected cell)?

Hafeez Esmail
P.S. This has been posted before but my question wasn't
quite answered correctly

 

[Jim Rech]

You can use the SpecialCells method to find the visible rows in an
autofilter. Maybe you can build something based on this example:


Sub a()
Dim Cell As Range
Dim Counter As Long
For Each Cell In Range("_FilterDatabase") _
.Columns(1).SpecialCells(xlCellTypeVisible)
Counter = Counter + 1
If Counter > 2 Then MsgBox Cell.Address
Next
End Sub

Excel always assigns the "_FilterDatabase" range name to a autofilter list.
The "> 2" is to skip (1) the headings and (2) your blank row.

 

[Hafeez Esmail]

Hi Jim, thanks for replying!
I added my code to yours but it doesn't give me what I
want.

Dim Cell As Range
Dim Counter As Long
For Each Cell In Range("_FilterDatabase").Columns(1) _
.SpecialCells(xlCellTypeVisible)
..SpecialCells(xlCellTypeVisible)
Counter = Counter + 1
If Counter > 2 Then
MsgBox Str(Counter), , "For Each"
Cell(Counter, 1).Select
dateA = ActiveCell.Value
dateB = ActiveCell.Offset(0, 1).Value
strDataD = ActiveCell.Offset(0, 3).Value
strDataN = ActiveCell.Offset(0, 13).Value
MsgBox "A/B = " & dateA & "/" & dateB
End If
Next
There are two things wrong.
1) it's going to every other cell
2) it's picking up data from every single cell (even ones
that are not visible)

Please help
Hafeez Esmail

 

[Jim Rech]

If you look at my code you'll see I do not do any "select"s or
"ActiveCell"s. My code works because the range "Cell" increments through
the range. That's what a For Each does. The pros do not use "Activecell"
unless they have to. This should run untouched with your filtered list.
Try to understand it and come back if you have any questions.

Sub a()
Dim Cell As Range
Dim Counter As Long
Dim dateA As Variant
Dim dateB As Variant
For Each Cell In Range("_FilterDatabase").Columns(1) _
.SpecialCells(xlCellTypeVisible)
Counter = Counter + 1
If Counter > 2 Then
dateA = Cell.Value
dateB = Cell.Offset(0, 1).Value
MsgBox "A/B = " & dateA & "/" & dateB
End If
Next
End Sub