Populate form from filtered range of rows

[time2innov8]

All,

I have written VBA code that populates values in a form that are verified/modified by the end user. When the form is loaded the user selects the spreadsheet to read the data from. The code copies the filtered data from the spreadsheet and populates my form with navigation achieved by "Next" & "Previous" buttons. Once all records have been read a button to enable the updated data to be save is enabled.
Everything works correctly however I need to do the same task with the filtered data in-place. i.e. Read through each row, allows modification where necessary and once all rows are processed, save the data.
How do I loop through the rows in a filtered range?
With the existing code I am just increasing a counter by 1, however that will not work with the data when filtered.

time2innov8

 

[Sektor]

Sub GenericCode()

    Dim rngFiltered As Range, rngVisible As Range, rng As Range
    
    Set rngFiltered = Range("A2:K5000")
    Set rngVisible = rngFiltered.SpecialCells(xlCellTypeVisible)
    
    For Each rng In rngVisible
        
        ' Process data here
        
    Next
    
End Sub

 

[time2innov8]

Sektor,

thanks for the reply, using a for loop does not allow straightforward selection of the rows using the "next" and "previous" buttons on the form. Would it be possible to initially read all the visible row numbers into an integer array, in that way I could use the existing code and just refer to the next array value to load/update the required row values from the spreadsheet?

time2innov8

 

[Sektor]

You can process either visible range or array.

Sub GenericCode()

    Dim arr As Variant
    Dim i As Long, lCount As Long
    Dim rngFiltered As Range, rngVisible As Range, rng As Range
    
    Set rngFiltered = Range("A2:K5000")
    Set rngVisible = rngFiltered.SpecialCells(xlCellTypeVisible)
    lCount = rngVisible.Count
    
    ' Get second range.
    MsgBox rngVisible(2)
    
    ' Get 10-th range.
    MsgBox rngVisible(10)
    
    ' Looping
    For i = 1 To lCount
        MsgBox rngVisible(i)
    Next
    
    ' Transfer range into array.
    arr = rngVisible
    
    ' Looping array.
    For i = 1 To UBound(arr)
        MsgBox arr(i)
    Next
    
End Sub

 

[time2innov8]

Sektor,

Your code examples helped me to resolve my problems.
As I only needed the row numbers I have modified the code in an ungainly manner as follows:

Dim rngFiltered As Range, rngVisible As Range, rng As Range

Set rngFiltered = Range("A2:K5000")
Set rngVisible = rngFiltered.SpecialCells(xlCellTypeVisible)
rowcount = 0

For Each Row In rngVisible.Rows
rowcount = rowcount + 1
Dim row_string As String
row_string = Row.Address(ReferenceStyle:=xlR1C1)
c_find = InStr(row_string, "C")
row_string = Mid(row_string, 1, c_find - 1)
row_string = Replace(row_string, "R", "")
row_array(rowcount) = CInt(row_string)

If Len(Cells(row_array(rowcount), "A")) < 1 Then
last_row = rowcount - 1
Exit For
End If
row_select.AddItem select_string, rowcount - 1
Next Row

There is going to be a more straightforward method to get the row other than the string manipulation I used but it achieved the end result.

time2innov8