Stepping through array elements

[Lenna]

Hello,

The code below creates an array of unique values from values in Column A. Each selected array element is used to select a range on the sheet (Col B). The range is displayed in a userform Listbox.

How do I allow the user to scroll through each array ‘MyarUniqVal’ element via two form buttons Right ‘>>’ and Left ‘<<’? Each time a button is pressed a sequential array item will be selected and a new range will populate the Listbox. I know how to cycle through array elements using a for each loop but I can't figure out how to increment only by one element in either direction each time a button is pressed.

Here is what I've got so far for selecting ranges:

Sub testRange3()


    Dim lastrow, i, j As Long
    Dim c As Range, rng As Range
    Dim MyArUniqVal() As Variant
    
   
ReDim MyArUniqVal(0)
'With ActiveSheet
 With ThisWorkbook.Worksheets("Temp")
 
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    
    For i = 1 To lastrow
        If .Cells(i, 1).Value <> .Cells(i + 1, 1).Value Then
            MyArUniqVal(UBound(MyArUniqVal)) = .Cells(i, 1).Value
            ReDim Preserve MyArUniqVal(UBound(MyArUniqVal) + 1)
        End If
    Next
    ReDim Preserve MyArUniqVal(UBound(MyArUniqVal) - 1)
End With
 
For j = LBound(MyArUniqVal) To UBound(MyArUniqVal)
'Prints out each array to Immediate Window
    Debug.Print j
'Prints out unique values from Column A stored in array to Immediate Window
    Debug.Print MyArUniqVal(j)
Next
   
   
With ThisWorkbook.Worksheets("Temp")
'changed to ActiveSheet
    'With ActiveSheet
        For Each c In .Range("A1:A" & lastrow)
For j = LBound(MyArUniqVal) To UBound(MyArUniqVal)
    If UCase(c.Text) = j Then
                'If UCase(c.Text) = "B" Then
                    If rng Is Nothing Then
                        Set rng = .Range("A" & c.Row).Resize(, 2)
                        Debug.Print rng
                    Else
                        Set rng = Union(rng, .Range("A" & c.Row).Resize(, 2))
                        Exit For
                        Debug.Print rng
                    End If
                End If
           Next
        Next c
    End With
 
    If Not rng Is Nothing Then rng.Select
 
End Sub

Thank you very much!

 

[Peter_SSs]

How do I bump this thread? Am I in the wrong forum?

reply with single word bump

.. but be patient (you are getting help for free ) & note #12 of the Forum Rules, particularly the second paragraph.

 

[MickG]

It might be better to select your ranges through a combobox filled with your array data!!!
It would also be easier to understand your requirements, if you could post the data that goes into your array and how it relates to the ranges you want to see in the List Box.