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:
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:
VBA 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