If statement

[jaypatel]

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

 

[rjp]

Try this and let me know if you have any trouble:

    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

 

[jaypatel]

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

 

[Dan71]

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.

 

[rjp]

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.

 

[Scott Huish]

Perhaps:

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