Visible Rows Indexing

  • Thread starter Thread starter John Wilson
  • Start date Start date
J

John Wilson

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
 
John,

You need to include the code that populates your listbox for us to
troubleshoot it.

HTH,
Bernie
MS Excel MVP
 
Bernie,

The code to populate the list box is working.
I'm using code that I got from Tom (it's attached at the bottom)

The code to get the ListBox Index is working too
(also attached below)

After I do a filter, I end up with a number of visible rows.
For example, let's say that the visible rows are 5 (header row)
and rows 8, 10 & 12.
The List box populates perfectly with the code.
When I double click the list box, I pass the ListBox Index
to a public variable (RemindLBI_No) and open up another
user form. I then want to use that Index number to get the
row number of one of the visible rows so that I can populate
some text boxes on the second user form with values on
that row.

Forgetting everything I explained above and the code attached below,
what I'm looking to do is capture the row number of 1st, 2nd and 3rd
visible row via an integer.
If rows 8, 16 & 23 are the only rows visible, how can I plug the
number 1 into a line of code and return 1, plug 2 in and get 16
and plug 3 in and get 23????

Thanks,
John

Coding below:

'Populate the ListBox:
Dim rng As Range
On Error Resume Next
Set rng = Range(Cells(6, 1), Cells(Rows.Count,
1).End(xlUp)).SpecialCells(xlVisible)
On Error GoTo 0
If Not rng Is Nothing Then
For Each cell In rng
ListBox1.AddItem cell.Value
ListBox1.List(ListBox1.ListCount - 1, 1) = cell.Offset(0,
1)
ListBox1.List(ListBox1.ListCount - 1, 2) = cell.Offset(0,
2)
Next
End If

'Get the ListBox Index and open the second user form
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
RemindLBI_No = ListBox1.ListIndex
EditReminder.Show
End Sub

' Populate the second User Form. Need to be able to plug the index number
' in the right place to index down the visible rows only.
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)
TextBox3.Value = Worksheets("Reminders").Range("C" & nVisRow)
End With
End Sub
 
correction....

If rows 8, 16 & 23 are the only rows visible, how can I plug the
number 1 into a line of code and return 8, plug 2 in and get 16
and plug 3 in and get 23????
 
John,

The function below will return the nth visible row, called as shown in
the example sub doit()

HTH,
Bernie
MS Excel MVP

Sub doit()
Dim myR As Range
Dim iRow As Integer

iRow = 3
Set myR = Range(Cells(6, 1), Cells(Rows.Count, 1).End(xlUp))
MsgBox "Visible row " & iRow & " is actual row " & _
GetVisibleRow(myR, iRow) & "."
End Sub

Function GetVisibleRow(myRange As Range, i As Integer) As Variant
Dim j As Integer
Dim myCell As Range
Set myRange = myRange.Offset(1, 0).Resize(myRange.Rows.Count - 1, 1)
j = 0
For Each myCell In myRange.SpecialCells(xlCellTypeVisible)
j = j + 1
If j = i Then
GetVisibleRow = myCell.Row
Exit Function
End If
Next
GetVisibleRow = "Not enough visible rows to return row " & i & "."
End Function
 
Bernie,

Was able to successfully modify your code to fit my needs.
Worked like a charm.

Many thanks,
John
 
You could also use a hidden column in your listbox and populate it with the
actual cell address or row when you load the listbox. Then just use that
value directly.
 
Tom,

Thanks for the suggestion. Hadn't thought of doing it quite that way
but I'm sure it would work.
For this particular dilemma that I had, capturing the displayed row was all
that I needed to do so that I could overwrite that same row with data
edited in a UserForm.
I did have to tweak Bernies code a little more to get around the
situation of none or one row being displayed in the filter but
I did finally get it to work the way I wanted it to.

Thanks again,
John
 
Back
Top