Delete rows based on a range of conditions (plural)

Question

it's been a while since I've used VBA. I have a range of unique values that I would like to search a table for. And if those values exist in said table, delete the entire row.

I'm able to loop through and delete specific, singular values, but struggling with multiple. I have tried replacing "30ExGEPAc30Q4" (code below) with Range() and an array, but can't quite get it. Here's what I've got so far:

Sub test()

Dim x As Long
Dim lastrow As Long

lastrow = Sheets("LRP").ListObjects("Data_LRP").Range.Rows.Count

Worksheets("LRP").Activate
    For x = lastrow To 1 Step -1
        If Cells(x, 1).Value = "30ExGEPAc30Q4" Then
            Rows(x).Delete
        End If
    Next x
End Sub

using `If`along with `And` like this: `If Cells(x, 1) = "30xGEPAc30Q4" And Cells(x, 2) = "Whatever" Then` Thought you are not referencing the `Cells` or `Rows` to any worksheet, so beware of that. You can nest as many conditionals as you want but that wouldn't be the way to go. — Damian, Mar 26 '19 at 16:59
@Damian, Thanks for the reply! I suppose i should have mentioned that my conditionals are a range of Cells, not static values. I was using the static string "30xGEPAc30Q4" to test if my delete rows loop was working as intended, but I hope to replace it with my dynamic cell range. Thanks for the help! — Nick, Mar 26 '19 at 17:09
Have a look here: https://stackoverflow.com/questions/12642164/check-if-value-exists-in-column-in-vba — Sam, Mar 26 '19 at 17:13
@Sam Thanks for the quick reply! That thread helps accomplish what I have above, but I'm looking to search & delete more than a single value. I'm looking for help replacing their singular search value with a range of values. Searching a range for any values that exist in another range. Thanks for the help! — Nick, Mar 26 '19 at 17:22
If you were going to use an array then you would add another loop after `For x = lastrow To 1 Step -1` to check each value in the array. e.g. `For i = LBound(Arr) To UBound(Arr)`, Your `If x = Arr(i)`, add `Next` below the `End If` — GMalc, Mar 26 '19 at 17:40

score 0 · Accepted Answer · answered Mar 26 '19 at 18:46

If I understand you correctly, this is what you're trying to achieve; I have cleaned up some of the unnecessary bits and now you just have to edit x and lastrow as is necessary.

Sub test()

Dim x As Long
Dim lastrow As Long

'lastrow = Sheets("LRP").ListObjects("Data_LRP").Range.Rows.Count
x = 1
lastrow = 21


'Worksheets("LRP").Activate
Do While x <= lastrow
'    For x = lastrow To 1 Step -1
        If Cells(x, 1).Value = "30ExGEPAc30Q4" Then
            Rows(x).Delete
            lastrow = lastrow - 1
        Else
            x = x + 1
        End If
'    Next x

Loop
End Sub

Thanks! I had to play with it a bit but the Do While Loop was the answer. I posted the working product below — Nick, Mar 26 '19 at 19:52

score 0 · Answer 2 · answered Mar 26 '19 at 19:51

For those curious, it ended up looking like this. Thanks for all the help!

Sub Cull()

Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim sht1row As Long
Dim sht2row As Long
Dim sht2total As Long
Dim DupID As String

Set sht1 = Worksheets("Data Form")
Set sht2 = Worksheets("LRP")
sht2.Activate
sht2total = Worksheets("LRP").ListObjects("Data_LRP").Range.Rows.Count
sht1row = 33

Do While sht1.Cells(sht1row, 2).Value <> ""

DupID = sht1.Cells(sht1row, 2).Value

    For sht2row = 2 To sht2total

        If DupID = Cells(sht2row, 1).Value Then

            Rows(sht2row).Delete
            sht2row = sht2row - 1
            Exit For

        End If

    Next

    sht1row = sht1row + 1

Loop

End Sub

Delete rows based on a range of conditions (plural)

2 Answers2