Loop through AutoFiltered range

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
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:

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
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
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.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
I've sussed it, I needed to add .cells(n) before the .value bit.

Thanks very much for your help.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
You're welcome, I,m glad you figured it out. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,798
Messages
5,626,951
Members
416,211
Latest member
lanka123

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
Top