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

"John Wilson" wrote in message
...
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

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



Bernie Deitrick wrote:

John,

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

HTH,
Bernie
MS Excel MVP

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 Wilson wrote:

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

Bernie Deitrick wrote:

John,

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

HTH,
Bernie
MS Excel MVP

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




"John Wilson" wrote in message
...
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 Wilson wrote:

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

Bernie Deitrick wrote:

John,

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

HTH,
Bernie
MS Excel MVP

Bernie,

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

Many thanks,
John

Bernie Deitrick wrote:

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

"John Wilson" wrote in message
...
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 Wilson wrote:

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

Bernie Deitrick wrote:

John,

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

HTH,
Bernie
MS Excel MVP

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.

--
Regards,
Tom Ogilvy

"John Wilson" wrote in message
...
Bernie,

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

Many thanks,
John

Bernie Deitrick wrote:

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

"John Wilson" wrote in message
...
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 Wilson wrote:

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

Bernie Deitrick wrote:

John,

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

HTH,
Bernie
MS Excel MVP

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

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"John Wilson" wrote in message
...
Bernie,

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

Many thanks,
John

Bernie Deitrick wrote:

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

"John Wilson" wrote in message
...
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 Wilson wrote:

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

Bernie Deitrick wrote:

John,

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

HTH,
Bernie
MS Excel MVP