How To:

  • Thread starter Kevin McCartney
  • Start date
K

Kevin McCartney

I have a data entry form that has Next, Previous buttons which navigate through the worksheet, my problem is that if the users leave a filter I only want to navigate through the visible rows, like using the cursor, the problem is that my code navigates through the hidden rows, so my questions is how to I make my code only navigate through the visible rows.

Application.Cells(Application.ActiveCell.Row + 1, Application.ActiveCell.column).Activate


TIA
KM
 
N

Norman Jones

Hi Kevin,

Adapting some autofilter code posted by Tom Ogilvy, perhaps this would help:

Sub SelectNextVisible()
Dim Rng As Range, Rng1 As Range
Dim iCol As Long
iCol = ActiveCell.Column
Set Rng = ActiveCell.CurrentRegion '<<< Amend to suit
Set Rng = Intersect(Rng, Columns(iCol))
Set Rng = Range(ActiveCell.Offset(1, 0), Rng(Rng.Count))

On Error Resume Next
Set Rng1 = Rng.SpecialCells(xlVisible)
On Error GoTo 0
If Not Rng1 Is Nothing Then
Rng1(1).Select
End If
End Sub


---
Regards,
Norman



Kevin McCartney said:
This works but is there a better way

Do
Application.Cells(Application.ActiveCell.Row + 1, Application.ActiveCell.column).Activate
Loop Until Not Application.ActiveSheet.Rows(Application.ActiveCell.Row).Hidden
Click to expand...
through the worksheet, my problem is that if the users leave a filter I only
want to navigate through the visible rows, like using the cursor, the
problem is that my code navigates through the hidden rows, so my questions
is how to I make my code only navigate through the visible rows.
 
T

Tom Ogilvy

That is probably as easy as any. Any other method would take a bit more
code I think.

You can do it without selecting or activating:

Dim rng as Range
set rng = ActiveCell
Do
set rng = ActiveCell.Offset(1,0)
Loop until rng.EntireRow.Hidden = False or rng.row = 50
if rng.EntireRow.Hidden = False then
rng.Select
else
msgbox "No more visible data"
End if


--
Regards,
Tom Ogilvy


Kevin McCartney said:
This works but is there a better way

Do
Application.Cells(Application.ActiveCell.Row + 1, Application.ActiveCell.column).Activate
Loop Until Not Application.ActiveSheet.Rows(Application.ActiveCell.Row).Hidden
Click to expand...
through the worksheet, my problem is that if the users leave a filter I only
want to navigate through the visible rows, like using the cursor, the
problem is that my code navigates through the hidden rows, so my questions
is how to I make my code only navigate through the visible rows.
 
Top