VBA How to Delete Rows when value of Column X does not match value of column Y

Question

I'm trying to delete every row where the cell value for column x does not match the cell value of column y. I keep getting an object error and I'm pretty sure I'm missing something obvious.

Sub Gooddata()

Application.ScreenUpdating = False

lr = Range("C65536").End(xlUp).Row
For a = lr To 1 Step -1
If Cells(a, 8).Value <> Cells(a, 9).Value Then
Cells(a, 3).Select
ActiveCell.EntireRow.Delete = True
End If
Next a

Application.ScreenUpdating = True
End Sub

Edit: I have made edits to the code based on the suggestions of the comments here's the input image the code and the output image.

Sub Gooddata()

Application.ScreenUpdating = False

lr = Range("C65536").End(xlUp).Row
For a = lr To 1 Step -1
If Cells(a, 8).Value <> Cells(a, 9).Value Then
Cells(a, 3).EntireRow.Delete
End If
Next a

Application.ScreenUpdating = True
End Sub

If the code executed in the way that I want it to Old Argus Building 12 will not be deleted however the row below it will because of the comma and dash.

Also, I recommend not using [`.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros).So, just snug up your line ending in `.Select` with the part after the next line's `Activecell.` So, `Cells(a, 3).EntireRow.Delete = True` ...which is what @Findwindow suggested. That's probably why you were getting the error (just if you were wondering). — BruceWayne, May 04 '16 at 18:45
So false alarm. It doesn't work as intended. When I tried @findwindow's the macro deleted a lot more than intended What am I doing wrong? When I ran the code with **Cells(a, 3).EntireRow.Delete** the macro deleted all cells aside from 3 where the 3 that were left the cells don't match at all.. I'm trying to delete rows where cell A and cell B DONT match. It seems to be doing the opposite. **<>** is the not equals operator in VBA isnt it? — Brayheart, May 04 '16 at 19:13
My apologies for being unclear and seriously thankyou for the help I really appreciate it. To clarify I am comparing columns H and I as you stated. What I want to do is have the macro identify if the cells are exactly the same. If they are not the entire row is deleted. — Brayheart, May 04 '16 at 19:30
@findwindow Yes I am. When I put in "= True" I get an object error so I took it out. It only runs without the "= True" part. When It does it seems to delete anything that is similar.. — Brayheart, May 04 '16 at 20:22
I added the photos in the original post. The first Picture is the input, the second is the output.. — Brayheart, May 04 '16 at 20:58
Do you mean **Trim (Cells(a, 3).EntireRow.Delete)**? It still has similar output to what I posted.. — Brayheart, May 04 '16 at 21:13
I'll be honest I'm not sure what **lr = Range("C65536").End(xlUp).Row** means.. Sadly the Trim function still does not fix the issue. But no worries :) thankyou for all youve done! — Brayheart, May 04 '16 at 21:22
I can't get this macro to do what I want, its so annoying :P — Brayheart, May 04 '16 at 21:35

findwindow · Answer 1 · 2016-05-04T22:40:08.713

Try this. This could break if second column has more words than first XD

Sub Gooddata()

Dim lr As Long
Dim arr As Variant
Dim brr As Variant

Application.ScreenUpdating = False

With ActiveSheet

lr = .Cells(.Rows.count, "G").End(xlUp).Row

For A = lr To 1 Step -1

    I = 0
    arr = Split(Trim(.Cells(A, 7).Value2), " ")
    brr = Split(Trim(.Cells(A, 8).Value2), " ")

    For Each e In arr

        '.Cells(A, 9) = e
        If e <> brr(I) Then
            .Cells(A, 3).EntireRow.Delete
            Exit For
            'GoTo yarr
        End If
        I = I + 1

    Next e

'yarr:
'Erase arr
'Erase brr

Next A

End With

Application.ScreenUpdating = True

End Sub

Before:

After:

VBA How to Delete Rows when value of Column X does not match value of column Y

1 Answers1

Linked