referencing filtered cells

[Hafeez Esmail]

Need professional help!
Please respond to this post if you know how to answer any
of the four questions below.
Backgound:
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.

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?
* Get data from cell in Column A in the same row as the
selected cell without switching the selected cell?
* Get data from cell in Column D in the next visible row
without switching the selected cell?

I think I'll have to set up a 'for loop' but the one
suggested to me (you can see it below) doesn't work. It
checks everyother row and cycels through visible and
hidden rows.

Please help

 

[Keith]

What does the original code look like? It says I can see
it below but I don't.

 

[Tom Ogilvy]

set cell = Range("A1")
do while cell.EntireRow.Hidden = True
set cell = cell.offset(1,0)
Loop
cell.Select

assuming we have selected the first visible cell

set cell = activecell.offset(1,0)
do while cell.EntireRow.Hidden = True
set cell = cell.offset(1,0)
Loop
cell.Select

msgbox ActiveCell.Value since the selection in is A

or
msgbox cells(activecell.row,1).Value

for D on next visible row

set cell = activeCell.offset
do while cell.EntireRow.Hidden = True
set cell = cell.offset(1,0)
Loop
msgbox cells(cell.row,4).Value

 

[Don Guillett]

Need professional help!
Since you asked for professional help, send Wild Turkey 101 litre.
Not sure about your "blank" in visible, so you may need to adjust.

Sub GetVisibleValues()
Set myRng = Range("A2", Cells(Rows.Count, 1)) _
.SpecialCells(xlCellTypeVisible)
x = myRng(1, 1).Address
MsgBox Range(x).Offset(0, 0)
MsgBox Range(x).Offset(0, 3)
End Sub