Loop through AutoFiltered range

[K1600]

I have code which is running an AutoFilter on existing data and is working fine. I am now trying to loop through each row of filtered data to be able to present it in a UserForm where the user then has the option to amend the data by choosing 'yes' or 'no' from a radio button. There are 18 rows of test data in my spreadsheet and when the AutoFilter runs that drops to 4 rows which is correct. When I run my current 'for' loop it only runs on the top two rows. When I have looked deeper into it, the loop starts correctly at the first filtered row but stops when the next row is not in the filtered range. So, my first two rows are 11 and 12 which run fine but then the next two in the filtered range are rows 17 and 18 (because 13 - 16 have been filtered out) which the loop never goes to as it stops after it completes the process on row 12.

I added the (xlCellTypeVisible) part which has stopped it looping through all 18 rows but I now just need it to loop all the filtered results.

If anyone can suggest anything I would be most grateful. This is my current code:

With Worksheets("Pro")
    Set rngColVRM = .Range("Table8[VRM / ID]").SpecialCells(xlCellTypeVisible)
    Set rngColDate = .Range("Table8[Date]").SpecialCells(xlCellTypeVisible)
    Set rngColDefect1 = .Range("Table8[Defect 1]").SpecialCells(xlCellTypeVisible)
    Set rngColDefect2 = .Range("Table8[Defect 2]").SpecialCells(xlCellTypeVisible)
    Set rngColDefect3 = .Range("Table8[Defect 3]").SpecialCells(xlCellTypeVisible)
    Set rngColMOTDate = .Range("Table8[Last MOT date]").SpecialCells(xlCellTypeVisible)
    Set rngColStatus = .Range("Table8[Status]").SpecialCells(xlCellTypeVisible)
    Set rngColStatusDate = .Range("Table8[Status Update Date]").SpecialCells(xlCellTypeVisible)
    Set rngColStatusOff = .Range("Table8[Updated by]").SpecialCells(xlCellTypeVisible)
    Set rngColDateMOTChecked = .Range("Table8[Date MOT checked]").SpecialCells(xlCellTypeVisible)
    Set rngColComplete = .Range("Table8[Complete]").SpecialCells(xlCellTypeVisible)
End With

Dim lastrow As Long
'Used to check number of responses in AutoFilter
    lastrow = ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
    Debug.Print lastrow
    If lastrow < 1 Then 'Stops process if there are no entries matching criteria of AutoFilter
        '    MsgBox ("Filter empty")
        Exit Sub
    Else
            UFCheck.TxtRowNumber.Text = lastrow
    End If

For r = 1 To rngColVRM.Rows.Count

    With UFCheck
    .TxtVRM.Text = rngColVRM.Cells(r).Value
    .TxtDate.Text = rngColDate.Cells(r).Value
    .TxtDefect1.Text = rngColDefect1.Cells(r).Value
    .TxtDefect2.Text = rngColDefect2.Cells(r).Value
    .TxtDefect3.Text = rngColDefect3.Cells(r).Value
    .TxtMOTDate.Text = rngColMOTDate.Cells(r).Value
    UFCheck.Show
        If .OptClearYes.Value = True Then
            rngColStatus.Value = "Reviewed - System generated"
            rngColStatusDate.Value = Date
            rngColStatusOff.Value = UserName & " - System generated"
            rngColComplete.Value = "Yes"
            Worksheets("Removed").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = rngColVRM.Cells(r).Value
            Worksheets("Removed").Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = Now
    
            .TxtVRM.Text = rngColVRM.Cells(r).Value
            .TxtDate.Text = ""
            .TxtDefect1.Text = ""
            .TxtDefect2.Text = ""
            .TxtDefect3.Text = ""
            .TxtMOTDate.Text = ""
            .OptClearNo.Value = False
            .OptClearYes.Value = False
            .TxtRowNumber.Text = .TxtRowNumber.Value - 1
        End If
    End With
Next

 

[K1600]

That's sort of worked. It now loops through all the filtered rows and if the radio button 'No' is pressed, all is good. However, if the 'Yes' is pressed, it should take the VRM from that row and add it to column 'A' in the 'Removed' sheet and then add a date/time stamp to column 'B' alongside it, as well as updating the columns in appropriate columns in the current row on the 'Pro' spreadsheet. What it is actually doing is taking the VRM from one of the hidden rows (row 10) and putting that in and then in the 'Pro' sheet it completes the relevant columns but does this for all 4 of the displayed rows rather than just the current row. If that makes any sense?

To ease identifying if the correct data is being dragged across I have added a consecutive number in ()'s to the end of each VRM.

To reset the original data you just need to clear columns P to S and AK but only on the filtered rows.

I've sorted the bit where it was transferring the wrong VRM across to the 'Removed' sheet, I needed to change rngColVRM.Cells(r).Value to rngColVRM.Cells(n).Value.

I'm still struggling with it copying the data down all the rows though.

 

[K1600]

I've sussed it, I needed to add .cells(n) before the .value bit.

Thanks very much for your help.

 

[Akuini]

You're welcome, I,m glad you figured it out.