# If statement

#### jaypatel

##### Active Member
Hi,

I have 2 columns A and B of course.....

I need a script that would do the following:

If a3=a2 and b3=b2 then delete row
if a3=a2 and b3>b2 then delete row b2
if a3>a2 then keep row a2..... in a kind of loop effect.

Any help would be appreciated.

Regards

Jay

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Try this and let me know if you have any trouble:

Code:
``````    Dim r As Long

r = 2
Do While Cells(r, "A").Value <> ""
If Cells(r, "A").Value = Cells(r - 1, "A").Value _
And Cells(r, "B").Value = Cells(r - 1, "B").Value Then
'This will delete the lower of the 2 rows, not sure I understood you here!
Rows(r).Delete
r = r - 1
ElseIf Cells(r, "A").Value = Cells(r - 1, "A").Value _
And Cells(r, "B").Value > Cells(r - 1, "B").Value Then
Rows(r - 1).Delete
r = r - 1
ElseIf Cells(r, "A").Value > Cells(r - 1, "A").Value Then
Rows(r).Delete
r = r - 1
End If
r = r + 1
Loop``````

Hi Rob,

Just tried it and it deleted all the rows..... thanks for trying.....

If you wish to clarify anything, i can send you the spreadsheet with a before and after.....

Regards

Jay

I guess you are tring to remove duplicuts. I just use a quick and dirty
=if(a2=a1,1,2)
Then just autofilter the 2s and delete all the rows.

Without seeing your data I can only guess where this is going wrong. Have a look at the If, ElseIf statements and make sure the logic matches your requirements, I may have misunderstood you. Check that my code deletes the correct row, should we delete r or r-1 etc.

Also, try stepping through the code with the F8 key, the error may be obvious when you see things step by step.

I have to leave soon but will check this thread tomorrow.

Perhaps:

Code:
``````Sub test()
Dim x As Long, y As Long
y = Range("A65536").End(xlUp).Row
For x = y To 3 Step -2
If Range("A" & x) = Range("A" & x - 1) Then
If Range("B" & x) = Range("B" & x - 1) Then
Range("A" & x).EntireRow.Delete
ElseIf Range("B" & x) > Range("B" & x - 1) Then
Range("A" & x - 1).EntireRow.Delete
End If
End If
Next x
End Sub``````

Replies
13
Views
335
Replies
6
Views
183
Replies
4
Views
474
Replies
3
Views
167
Replies
5
Views
221

1,220,987
Messages
6,157,236
Members
451,407
Latest member
vdaesety

### 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.

### Which adblocker are you using?

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

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