# Thread: Deleting rows based on values in two cells (2007)

1. ## Deleting rows based on values in two cells (2007)

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)) < 3000 Then
Range("G" & r).EntireRow.Delete
End If
Next r
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?

2. ## Re: Deleting rows based on values in two cells (20

As for #2

You are missing a final End If:

...
End If
End If
Next r
End Sub

3. ## Re: Deleting rows based on values in two cells (2007)

Jezza already pointed out the omission in your code. You could combine the conditions as follows:
<code>
For r = n To 2 Step -1
If Trim(Range("G" & r)) < 3000 Or 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</code>

4. ## Re: Deleting rows based on values in two cells (2007)

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

Now back to custonizing the ribbon for this add-in... <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•