Step to next row in a filtered sheet using form

  • Thread starter Thread starter Steen
  • Start date Start date
S

Steen

Hi

Hope for some help to the following issue:

I have a form that I use to enter/edit information in a excel sheet. This
works ok. But somtime I use filters in the sheet, but when the form is still
stepping through the filtered (not shown rows). I want it to step only
through the filtered list/rows.

The code for stepping forward in the form are shown below - any help will be
much appriciatied.

Private Sub cmdNext_Click()
On Error Resume Next ' ignore error for the moment
ActiveCell.Offset(1, 0).Activate
If Err <> 0 Then
Err.Clear ' trash the error
End If
On Error GoTo 0 ' resume normal error trapping

If ActiveCell.Value <> "" And ActiveCell.Row > 4 Then
Call Initialize_Form
End If
End Sub

/Steen
 
Sub filteredRange()
Const startingRow = 2
Dim rng As Range
Dim i As Long

Set rng = Range(Cells(startingRow, "A"), Cells(Rows.Count, "A"))
For Each c In rng
If Not c.EntireRow.Hidden Then
MsgBox c.Value
End If
Next c
End Sub
 
Hi Mike

Thanks for your quick answer.

I sorry, but I don't get the point in your answer. Would it be possible for
you to add a few comments on your answer - I have a little trouble in seeing
how to use the FilterdRange() macro in my part of the code?

/Steen
 
Im not sure what your exactly what your looking for but maybe this
Private Sub cmdNext_Click()
On Error Resume Next ' ignore error for the moment
If ActiveCell.EntireRow.Hidden Then
Do While ActiveCell.EntireRow.Hidden
ActiveCell.Offset(1, 0).Activate
Loop
Else
ActiveCell.Offset(1, 0).Activate
End If
If Err <> 0 Then
Err.Clear ' trash the error
End If
On Error GoTo 0 ' resume normal error trapping

If ActiveCell.Value <> "" And ActiveCell.Row > 4 Then
Call Initialize_Form
End If

End Sub
 
Spot on - thanks a lot Mike. :-)

Mike said:
Im not sure what your exactly what your looking for but maybe this
Private Sub cmdNext_Click()
On Error Resume Next ' ignore error for the moment
If ActiveCell.EntireRow.Hidden Then
Do While ActiveCell.EntireRow.Hidden
ActiveCell.Offset(1, 0).Activate
Loop
Else
ActiveCell.Offset(1, 0).Activate
End If
If Err <> 0 Then
Err.Clear ' trash the error
End If
On Error GoTo 0 ' resume normal error trapping

If ActiveCell.Value <> "" And ActiveCell.Row > 4 Then
Call Initialize_Form
End If

End Sub
Click to expand...
 
Back
Top