Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and delete
Hi, have a workbook with 2 spreadsheets, first one is the master list (with
unique references), second one is an extract (containing duplicated data). Can anyone help me with a way of looking at the unique references on sheet 2, finding the duplicate reference on sheet 1 and deleting the row from sheet 1?? Any help much appreciated Philip |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and delete
Philip
Is "Sheet 2" the "Master" list? What column(s) do you want to look at to determine if the listings are the same? Do you want to delete from the Master list or from the other sheet? HTH Otto "Philip Drury" wrote in message ... Hi, have a workbook with 2 spreadsheets, first one is the master list (with unique references), second one is an extract (containing duplicated data). Can anyone help me with a way of looking at the unique references on sheet 2, finding the duplicate reference on sheet 1 and deleting the row from sheet 1?? Any help much appreciated Philip |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and delete
Sheet 1 is the Master list (of accounts), sheet 2 is the list of paid
accounts - these rows need to be removed from the Master list (sheet 1). The column with the the unique references is J on both sheets. Thanks Philip "Otto Moehrbach" wrote: Philip Is "Sheet 2" the "Master" list? What column(s) do you want to look at to determine if the listings are the same? Do you want to delete from the Master list or from the other sheet? HTH Otto "Philip Drury" wrote in message ... Hi, have a workbook with 2 spreadsheets, first one is the master list (with unique references), second one is an extract (containing duplicated data). Can anyone help me with a way of looking at the unique references on sheet 2, finding the duplicate reference on sheet 1 and deleting the row from sheet 1?? Any help much appreciated Philip |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find and delete
Philip
This macro should do what you want. Note that I chose to name your Sheet1 "One" and your Sheet 2 "Two". I assumed that your data started in row 2. Change these parameters to suit your data. HTH Otto Sub DeleteDupRows() Dim RngJSht2 As Range Dim RngJSht1 As Range Dim i As Range Application.ScreenUpdating = False Sheets("Two").Select Set RngJSht2 = Range("J2", Range("J" & Rows.Count).End(xlUp)) With Sheets("One") Set RngJSht1 = .Range("J2", .Range("J" & Rows.Count).End(xlUp)) End With For Each i In RngJSht2 On Error Resume Next RngJSht1.Find(What:=i.Value, LookAt:=xlWhole).EntireRow.Delete On Error GoTo 0 Next i Application.ScreenUpdating = True End Sub "Philip Drury" wrote in message ... Sheet 1 is the Master list (of accounts), sheet 2 is the list of paid accounts - these rows need to be removed from the Master list (sheet 1). The column with the the unique references is J on both sheets. Thanks Philip "Otto Moehrbach" wrote: Philip Is "Sheet 2" the "Master" list? What column(s) do you want to look at to determine if the listings are the same? Do you want to delete from the Master list or from the other sheet? HTH Otto "Philip Drury" wrote in message ... Hi, have a workbook with 2 spreadsheets, first one is the master list (with unique references), second one is an extract (containing duplicated data). Can anyone help me with a way of looking at the unique references on sheet 2, finding the duplicate reference on sheet 1 and deleting the row from sheet 1?? Any help much appreciated Philip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and delete! | Excel Discussion (Misc queries) | |||
Find and Delete | Excel Worksheet Functions | |||
find a value and delete that row | Excel Discussion (Misc queries) | |||
find row and delete | Excel Discussion (Misc queries) | |||
Find delete | Excel Discussion (Misc queries) |