Delete row if columnA and ColumnB are same

[Lillian]

I have one big excel spreed sheet, on the ColumnA is call
staddr, and ColumnB is call mailaddr, if both column as
same address, example "123 main street" then I need to
delete entire row, can everyone show me how to write with
VB script. I will be really appreciated it.


Lillian

 

[Frank Kabel]

Hi
try something like the following:
Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
For RowNdx = LastRow To 1 Step -1
with Cells(RowNdx, "A")
if .value = .offset(0,1).value then
Rows(RowNdx).Delete
End If
end with
Next RowNdx
Application.ScreenUpdating = True
End Sub

 

[Guest]

Why not try this using Access, much easier. Just import
the spread sheet, run a query to find matches and delete
the results. Then you can TransferSpreadSheet back out to
Excel if you need to.

 

[Lillian]

Thanks it work, the offset (0,1) is refer to ColumnB, am
I right? if Column A want to match to ColumnC then I need
to set the offset (0,2), am I correct?


Lillian

 

[Frank Kabel]

Hi
yes, you're correct