Move up and down fintered list via User Form

Barlew2

New Member
Joined
Jan 1, 2021
Messages
8
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
I've a User Form that allows the user to filter a list and view single record information (rows) within the User form. Using arrows on the User Form, they can go to the next visible record (row) in the filtered list to view that information. Both the code examples I found from THIS thread work great for going to the next visible row, but neither work for going back up! I tried changing Offest(1) to Offset(-1) but it didn't work.

This is the code I've currently. Arrow 1 should move to the previous visible row and retrieve the row number and assign to ProcRow (ProcRow has already been defined during the user's initial search and item selection).

VBA Code:
  If Arrow = 1 Or Arrow = 2 Then
        If Arrow = 1 Then
            Set cel = Sheet2.Range("C" & ProcRow)
            ProcRow = Range(cel.Offset(-1), cel.Parent.Cells(Rows.Count, cel.Column)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row ###(Not working)###
        Else
            Set cel = Sheet2.Range("C" & ProcRow)
            ProcRow = Range(cel.Offset(1), cel.Parent.Cells(Rows.Count, cel.Column)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row ###(Working)###
        End If
End If

In a nutshell, I'm looking for code to move up or down a filtered list and retrieve the row number when the user selects either Arrow 1 (up) or Arrow 2 (down).

Thank you
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Hi & welcome to MrExcel.
How about
VBA Code:
   If Arrow = 1 Or Arrow = 2 Then
        If Arrow = 1 Then
            Set cel = Sheet2.Range("C1", Sheet2.Range("C" & ProcRow - 1)).SpecialCells(xlVisible)
            ProcRow = Split(cel.Address, "$")(UBound(Split(cel.Address, "$")))
        Else
            Set cel = Sheet2.Range("C" & ProcRow)
            ProcRow = Range(cel.Offset(1), cel.Parent.Cells(Rows.count, cel.Column)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row
        End If
   End If
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0
Sorry Fluff, thought i could add error handling when row 1 was returned or the last entry in filtered list was detected, but im struggling!

Sudocode:
If arrow 1 (move up visible row) is selected, if the result of the routine is Row 1, don't do anything
If arrow 2 (move down visible row) is selected, if there are no more result items in visible list, don't do anything (in my code Rwcnt is a count of visible rows, if that helps)

Thank you
 
Upvote 0
How about
VBA Code:
   If Arrow = 1 Or Arrow = 2 Then
        If Arrow = 1 Then
            If ProcRow = 1 Then Exit Sub
            Set cel = Sheet2.Range("C1", Sheet2.Range("C" & ProcRow - 1)).SpecialCells(xlVisible)
            ProcRow = Split(cel.Address, "$")(UBound(Split(cel.Address, "$")))
        Else
            Set cel = Sheet2.Range("C" & ProcRow)
            ProcRow = Sheet2.Range(cel.Offset(1), Sheet2.Cells(Rows.count, cel.Column)).SpecialCells(xlVisible).Cells(1, 1).Row
            If ProcRow >= Sheet2.Range("C" & Rows.count).End(xlUp).Row Then Exit Sub
        End If
   End If
 
Upvote 0
Moving down worked with the '.End(xlUp)' command, but moving up didn't. Looking at your code, at the point 'If ProcRow = 1' was introduced, the ProcRow variable is still assigned to the previous row and doesn't get updated until after next 2 steps. In testing, selecting the Arrow 1, up, and stepping through, debugging, the code, before the line highlighted below ProcRow equals 2, which it should, then passing the line it equals 88?
VBA Code:
ProcRow = Split(cel.Address, "$")(UBound(Split(cel.Address, "$")))
Odd!

I've tried adding this after the above code:
VBA Code:
If cel = Sheet2.Range("C1", Sheet2.Range("C" & ProcRow - 1)) = "Short Name" Then Exit

"Short Name" is the text in C1, but this doesn't work either?
 
Upvote 0
Ok, how about
VBA Code:
            If ProcRow <= 2 Then Exit Sub
 
Upvote 0
Chears Fluff, that worked. Time for some more testing!
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top