Down One Row

[Guest]

Usually I can navigate a spreadsheet by getting the row # and going down 1 row:
x=activecell.row
x=x+1
cells(x,1).select
So if I was on row 25 now I am on row 26. But what if the data is filtered
and row 26 is not in the filter. I want to go down 1 row but how can I know
which row that is?

 

[Guest]

Try something like this:

Sub DownToNextVisibleCell()
Dim lngCtr As Long
Dim lngCurrCol As Long

lngCurrCol = ActiveCell.Column

For lngCtr = ActiveCell.Row + 1 To Rows.Count
With Cells(RowIndex:=lngCtr, ColumnIndex:=lngCurrCol)
If .EntireRow.Hidden = False Then
.Select
Exit For
End If
End With
Next lngCtr
End Sub


Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP

 

[Guest]

That works perfectly. Thanks a lot.

 

[Bob Phillips]

What do you mean by how can you know? You won't see it in Excel, because you
have selected a non-visible row, but it is still active, and

?activecell.Row


returns that row in the immediate window.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

I'm glad I could help, Mike........Thanks for the feedback.

***********
Regards,
Ron

XL2003, WinXP