Data List Visible Rows
 

Hi,

I have a user form which acts as a DB front end for a spreadsheet and I
want it to skip any rows hidden by AutoFilter. I constructed the following
code based on chapter 9 in "Excel 2003 VBA Programmer's reference" . The code
loops through the next rows in the range and tests if they are hidden.
Unfortunately, it doesn't work as expected and seems to skip rows that aren't
hidden and show ones that are.

Private Sub cmdNextRecord_Click()
Dim i As Integer
i = 1
With Range("Database")
Do While RangeData.Rows(Navigator.Value + i).EntireRow.Hidden = True
i = i + 1
Loop
If RangeData.Row < .Rows(.Rows.Count).Row Then
'Load next record only if not on last record
Navigator.Value = Navigator.Value + i
End If
End With
End Sub

Regards
JT

Data List Visible Rows
 

Without taking anything away of the esteemed authors of that fine book, this
is how I'd iterate through a database skipping hidden rows:

Dim Rw As Range
For Each Rw In Range("Database").SpecialCells(xlCellTypeVisible). Rows
Debug.Print Rw.Row
Next


--
Jim Rech
Excel MVP
"JT3686" wrote in message
...
Hi,

I have a user form which acts as a DB front end for a spreadsheet and I
want it to skip any rows hidden by AutoFilter. I constructed the following
code based on chapter 9 in "Excel 2003 VBA Programmer's reference" . The
code
loops through the next rows in the range and tests if they are hidden.
Unfortunately, it doesn't work as expected and seems to skip rows that
aren't
hidden and show ones that are.

Private Sub cmdNextRecord_Click()
Dim i As Integer
i = 1
With Range("Database")
Do While RangeData.Rows(Navigator.Value + i).EntireRow.Hidden =
True
i = i + 1
Loop
If RangeData.Row < .Rows(.Rows.Count).Row Then
'Load next record only if not on last record
Navigator.Value = Navigator.Value + i
End If
End With
End Sub

Regards
JT