Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if columnA and ColumnB are same
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if columnA and ColumnB are same
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 -- Regards Frank Kabel Frankfurt, Germany Lillian wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if columnA and ColumnB are same
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. -----Original Message----- 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if columnA and ColumnB are same
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 -----Original Message----- 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 -- Regards Frank Kabel Frankfurt, Germany Lillian wrote: 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if columnA and ColumnB are same
Hi
yes, you're correct :-) -- Regards Frank Kabel Frankfurt, Germany Lillian wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF value columnA columnB | Excel Worksheet Functions | |||
sum up columnA depending on conditions on columnB and columnC | Excel Discussion (Misc queries) | |||
Count cells that contain "Y" in columnA IF contains"X" in columnB | Excel Worksheet Functions | |||
Adding new numbers as I type without duplicates from Sheet1,ColumnA to Sheet2,ColumnA | Excel Worksheet Functions | |||
Delete row if columnA and ColumnB are same | Excel Programming |