View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
John Wilson John Wilson is offline
external usenet poster
 
Posts: 550
Default Visible Rows Indexing

In a quagmire again.......

Triying to index down through visible rows on a filtered range.

The code:

Private Sub UserForm_Activate()
With ActiveCell
nVisRow = Range(.Offset(RemindLBI_No), Cells(65536, .Column)) _
.SpecialCells(xlCellTypeVisible).Cells(1).Row
TextBox1.Value = Worksheets("Reminders").Range("A" & nVisRow)
TextBox2.Value = Worksheets("Reminders").Range("B" & nVisRow)
End With
End Sub

ActiveCell is always Range ("A5") <the header row
"RemindLBI_No" is the ListBox Index number (from a ListBox on another
UserForm)

Here's the problem......
Let's say that my filter leaves rows 8, 10 & 12 visible.
If I select the first item in the ListBox.Index = 1) it works fine.
(nVisRow = 8)
If I select the second item, (ListBox.Index = 2) it still works.
(nVisRow = 10)
If I select the last item, (ListBox.Index = 3), it doesn't work.
(nVisRow = 10)

I understand why the above doesn't work but am at a loss to find a
workaround.
In essence, starting from row 5, I need to get the row number of the 3rd

visible row below row 5.

TIA for any insight into getting this to work,
John