Delete row if columnA and ColumnB are same

  • Thread starter Thread starter Lillian
  • Start date Start date
L

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
 
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
 
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.
 
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
 
Back
Top