# VBA/Formula for comparing three or four tables

#### Vishaal

##### Active Member
Hi,

i have three columns and want to compare them, conditions are

1- If Coloumn 1 & Coloumn 2 are equal - Delete entire row
2- If Coloumn 2 & Coloumn 3 are equal - Delete entire row
3- If Coloumn 1 & Coloumn 3 are equal - Delete entire row

please provide any VBA or code or formula

#### My Aswer Is This

##### Well-known Member
Try this:
Code:
``````Sub Delete_If()
'Modified 8/26/2019 3:47:12 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = Lastrow To 1 Step -1
If Cells(i, 1).Value = Cells(i, 2).Value Then Rows(i).Delete
Next
Application.ScreenUpdating = True
End Sub``````

#### Vishaal

##### Active Member
Thanks bro

its working for me

#### My Aswer Is This

##### Well-known Member
Come back here to Mr. Excel next time you need additional assistance.
Thanks bro

its working for me

#### JackDanIce

##### Well-known Member
It's unclear in the suggested code #2 where blue conditions are tested:
1- If Coloumn 1 & Coloumn 2 are equal - Delete entire row
2- If Coloumn 2 & Coloumn 3 are equal - Delete entire row
3- If Coloumn 1 & Coloumn 3 are equal - Delete entire row
The loop seems to only compare columns 1 and 2
Rich (BB code):
``````For i = lastRow To 1 Step -1
If Cells(i, 1).Value = Cells(i, 2).Value Then Rows(i).Delete
Next``````
vs
i have three columns and want to compare them, conditions are

If you need to compare column 1 vs column 3 or column 2 vs column 3 as well as column 1 vs column 2 then try:
Rich (BB code):
``````Sub M1()

Dim v   As Variant
Dim x   As Long

x = Cells(Rows.Count, 1).End(xlUp).Row
v = Cells(1, 1).Resize(x, 3).Value

For x = LBound(v, 1) To UBound(v, 1)
If v(x, 1) = v(x, 2) Or v(x, 1) = v(x, 3) Or v(x, 2) = v(x, 3) Then v(x, 1) = ""
Next x

With Cells(1, 1).Resize(UBound(v, 1))
.Resize(, UBound(v, 2)).Value = v
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With

Erase v

End Sub``````

Last edited:

#### Peter_SSs

##### MrExcel MVP, Moderator
It's unclear in the suggested code #2 where blue conditions are tested:

The loop seems to only compare columns 1 and 2
I agree, but perhaps the OP adapted to add the other conditions.

I disagree with your suggested alternative though as it would delete row 4 from the following sample data even though that row does not meet the delete conditions. Of course you could perhaps set v(x, 1) to an error value & then delete the error rows (assuming no errors already in column A).

Excel Workbook
ABC
1Hdr1Hdr2Hdr3
2xcq
3fgf
4jg
5yyy
6h
Compare columns

If the data is not too large, here is a non-looping approach. I have assumed ..
- no data beyond column C

Code:
``````Sub Del_Matches()
Application.ScreenUpdating = False
With Range("A1:C" & Columns("A:C").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row)
Range("D2").Formula = "=OR(A2=B2,B2=C2,A2=C2)"
.Offset(1).EntireRow.Delete
Range("D2").ClearContents
End With
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
Application.ScreenUpdating = True
End Sub``````

@Vishaal
If the data is very large, post back for another faster approach & tell us about how many rows of data you might actually have.

#### Vishaal

##### Active Member
Thanks JackDanIce Sir

#### Vishaal

##### Active Member
Thanks Peter_SSs Sir Ji

I have only Four Coloumn in some and only eight coloumn in some

and raw for all will be row 2 to row 10000

#### Peter_SSs

##### MrExcel MVP, Moderator
I have ... eight coloumn in some

and raw for all will be row 2 to row 10000
So, have you tried any of the suggestions with 8 columns and 10,000 rows?

- If not, I suggest that you do and report back on the time taken

-If so, was the code fast enough (about how long?) and exactly what code did you use since none of the suggestions were based on 8 columns.

For the future I also suggest that you try to be more accurate with your initial information. In this thread you stated specifically "i have three columns"

#### JackDanIce

##### Well-known Member
Hey @Peter_SSs
I agree, but perhaps the OP adapted to add the other conditions.

How many times on this forum have we seen replies complaining OP hasn't given enough precise information AND after a suggestion has been posted (and thanked by the OP) others have contributed with their alternatives or different assumptions/interpretations of the ask?

By extention your reply is inferring the first suggestion should be sufficient alone and so not provide alternatives.

A reverse loop vs an array loop are both different yet used for same outcome, so even if the OP had adapted, it would be a different way to consider a solution and as you did with your code, suggest an alternative solution that is hopefully faster than the initial posted suggestion?

OP could have run code where only the first condition satisfies but if data size is large, not OP didn't scroll down far enough, there may be other rows that should be deleted but were not, because it's only comparing columns 1 & 2 and the User experienced a unknown known?

I disagree with your suggested alternative
Great spot, I hadn't tested for that scenario - I (wrongly!) assumped column A to not be blank

Finally, their follow up with the unclear,
I have ... eight column in some
suggests they hadn't adapted the suggestion or considered in relation to the initial code suggestions, so better to not assume they adapated the code, especially given the initial post does state for 3 columns AND the first suggestion only compared columns A&B exclusively?

Last edited:

1,082,305
Messages
5,364,400
Members
400,800
Latest member
germanpbv

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...