Comparing 2 rows for duplicate data

  • Thread starter Thread starter bkbri
  • Start date Start date
B

bkbri

Hello,

Can someone please help me. Im looking for a Macro that will sca
through columns A - B for matching entries and delete those it finds i
column A.

___A___B__
1.| N | H |
2.| B | I |
3.| C | J |
4.| D | K |
5.| E | L |
6.| F | M |
7.| G | N |<--- N found deleting duplicate in cell A1

Thanks
 
Just so that I understand, you want to delete any value in
column B that is found in Column A. Is that correct?

Sub Sample()
Set ColAValues = ActiveSheet.Range("A1:A" &
ActiveSheet.Range("A65536").End(xlUp).Row)
ColBLastRow = ActiveSheet.Range("B65536").End(xlUp).Row

For i = ColBLastRow To 1 Step -1
CurrentValue = ActiveSheet.Cells(i, 2).Value
For Each Cell In ColAValues
If Cell.Value = CurrentValue Then
ActiveSheet.Cells(i, 2).Delete shift:=xlUp
End If
Next Cell
Next i
End Sub
 
Hello thanks for the help this is exacly what I wanted. I'm going try i
now.

Thank you so much,
Bria
 
Hi Tod,

I just tried the macro and I get this error message.

Compile Error
Syntax Error

Also this portion of your code is highlighted in red

Set ColAValues=ActiveSheet.Range("A1:A" &
ActiveSheet.Range("A65536").End(xlUp).Row)

These are the steps I took to create your macro

I went to Tools, Macro, Visual Basic Editor, Insert, Module and paste
the code into the large white window and then pressed the play button
 
bkbri,
change
Set ColAValues=ActiveSheet.Range("A1:A" &
ActiveSheet.Range("A65536").End(xlUp).Row)
to
Set ColAValues=ActiveSheet.Range("A1:A" & _
ActiveSheet.Range("A65536").End(xlUp).Row)
to run the macro
from your worksheet
Tools>Macro>(select the macro you want run and click run)
Cecil
 
Back
Top