View Full Version : Solved: compare two columns and delete matches

01-17-2011, 10:52 PM
I would like to compare column D and column F.
If the content in a cell in column F matches with a cell content in column D,
Then delete the cell in column F
Also delete the corresponding cell in column G
Take a look at the image below

I have attached a sample workbook
Sheet 1 contains the initial set of data
Sheet 2 contains a button to insert the codes
Sheet 3 shows how the final output looks like

01-18-2011, 01:04 AM
Your result sheet left out some matching results so not sure of your requirement. See if this works:
Private Sub CommandButton1_Click()
Dim LastRowD, LastRowF As Long
With Sheet1
LastRowD = .Range("D" & Rows.Count).End(xlUp).Row
For i = 1 To LastRowD
LastRowF = .Range("F" & Rows.Count).End(xlUp).Row
MyVal = .Range("D" & i).Value
For j = LastRowF To 1 Step -1
If .Range("F" & j).Value = MyVal Then
.Range("F" & j).Resize(, 2).Delete Shift:=xlUp
End If
Next j
Next i
End With
End Sub

01-18-2011, 02:00 AM
Thanks Shrivallabha for your help. It works. I think i missed out the other matches.

01-18-2011, 02:22 AM
Hi rafi,

Try the attached.