VBA clear contents from cells in row if range of rows in column match range from column in another s-CodePudding

I think I am very close but I cant figure out how to solve this issue. Everything looks right. I have data sht1 column "T2" with "MO123" Which matches 1 of row in column B on Sht2, not sure if its complaining about the .ClearContent or my range. Thank you for any help you can give me. I will add the code just in case you want to try it out. -Paul

Sub Delete_Picklist_Rec()
    Dim Sht1 As Worksheet, Sht2 As Worksheet
    Dim LstRw As Long, lookup_rng As Range, x


    Set Sht1 = Sheets("Pick Ticket Schedule")
    Set Sht2 = Sheets("Pick Ticket (History)")
    Set lookup_rng = Sht2.Range("B2:B7")

    With Sht1
        LstRw = .Cells(.Rows.Count, "T").End(xlUp).Row
        MsgBox "LstRw:" & LstRw
        For x = LstRw To 1 Step -1
            If .Cells(x, 1) = lookup_rng Then
                Range("J" & x & ":K" & x).ClearContents Shift:=xlUp 'Need to delete value in cell J & K for the row that matches
            End If
        Next x

    End With

End Sub

CodePudding user response：

For the sake of there being an answer so everyone knows it's solved:

The first issue was Shift:=xlUp isn't a argument for ClearContents (it has no arguments) so removing that allows the clear to work. Alternatively you can use .Delete instead and utilize the Shift argument then.

Secondly you didn't specify the sheet for your Range so it by default used the Active Sheet.

Thirdly, you're comparing a single cell value to a range which needs to be done either by a find command or looping. I've used the Find command as it's typically the fastest solution for something like this. It also has more arguments than what I've used so if needed you can add them to it. Check out more about the find command here.

Here is the solution based on what I have said:

Sub Delete_Picklist_Rec()
    Dim Sht1 As Worksheet, Sht2 As Worksheet
    Dim LstRw As Long, lookup_rng As Range, x As Long


    Set Sht1 = Sheets("Pick Ticket Schedule")
    Set Sht2 = Sheets("Pick Ticket (History)")
    Set lookup_rng = Sht2.Range("B2:B7")

    With Sht1
        LstRw = .Cells(.Rows.Count, "T").End(xlUp).Row
        MsgBox "LstRw:" & LstRw
        For x = LstRw To 1 Step -1 'Doesn't need to loop backwards if just clearing but does if using Delete.
            If Not lookup_rng.Find(what:=.Cells(x,1), LookIn:=xlValues, lookat:=xlWhole) Is Nothing Then
                .Range("J" & x & ":K" & x).ClearContents 'Or .Delete Shift:=xlUp
            End If
        Next x

    End With

End Sub