Deleting rows based on values in two cells (2007)

Topic

New Reply

Good morning,
I am trying to write a macro that will delete a row if the value in column “Z” is 3200 AND the value in column “F” is “—–“. I have tried using samples of code from this forum that delete rows and found they work well for the rows where I have only one criteria to meet for deletion, but the second criteria has me baffled. Here is the code that I am currently trying but I am getting a compile error: Next without For and cannot see why.
Dim r As Long
Dim n As Long
Dim o As Long

n = Range(“G65536”).End(xlUp).Row
For r = n To 2 Step -1
If Trim(Range(“G” & r)) 3899 Then
Range(“G” & r).EntireRow.Delete
End If
Next r
For r = n To 2 Step -1
If Trim(Range(“G” & r)) = 3200 Then
If Trim(Range(“F” & r) = “—–“) Then
Range(“G” & r).EntireRow.Delete
End If
Next r

I have three questions based on this code.
#1 Will it actually delete a row that has two criteria?
#2 Why am I getting the compile error?
#3 Is there a way to streamline the code?

Reply | Quote

Replies

As for #2

You are missing a final End If:

…
End If
End If
Next r
End Sub

Reply | Quote

Jezza already pointed out the omission in your code. You could combine the conditions as follows:

For r = n To 2 Step -1
If Trim(Range("G" & r)) 3899 Or _
Trim(Range("G" & r)) = 3200 And Trim(Range("F" & r) = "-----") Then
Range("G" & r).EntireRow.Delete
End If
Next r

Reply | Quote

Thank you to both Jezza and Hans. The code worked beautifuly.

Now back to custonizing the ribbon for this add-in…

Reply | Quote