Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to compare two list
Hi! I have two worksheets. On the Master list I have a list of cars wit their Registration numbers and vehicle details, the other has jus registrations only. I wish to compare the two lists, and remove fro the Master list any vehicle that is on the registration list only. I've been able to use the Macro from the Microsoft website, but thi only removes the duplicate registration numbers, not the entire ro relating to this registration number. Has anyone any idea how I modif this macro to delete the row, not just the cell? Sub DelDups_TwoLists() Dim iListCount As Integer Dim iCtr As Integer ' Turn off screen updating to speed up macro. Application.ScreenUpdating = True ' Get count of records to search through (list that will be deleted). iListCount = Sheets("Master").Range("A1:A2500").Rows.Count ' Loop through the "master" list. For Each x In Sheets("Sheet2").Range("A1:A2500") ' Loop through all records in the second list. For iCtr = 1 To iListCount ' Do comparison of next record. ' To specify a different column, change 1 to the column number. If x.Value = Sheets("Master").Cells(iCtr, 1).Value Then ' If match is true then delete row. Sheets("Master").Cells(iCtr, 1).Delete xlShiftUp ' Increment counter to account for deleted row. iCtr = iCtr + 1 End If Next iCtr Next Application.ScreenUpdating = True MsgBox "Done!" End Sub Thanks in advance Darre -- DGillha ----------------------------------------------------------------------- DGillham's Profile: http://www.excelforum.com/member.php...fo&userid=1370 View this thread: http://www.excelforum.com/showthread.php?threadid=56016 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to compare two list
Hello Darren, To delete the entire row this line needs to be changed from: Sheets("Master").Cells(iCtr, 1).Delete xlShiftUpHere is the revised to: Sheets("Master").Cells(iCtr, 1).EntireRow.Delete xlShiftUpcode Here is the revised code... Code ------------------- ' Loop through the "master" list. For Each x In Sheets("Sheet2").Range("A1:A2500") ' Loop through all records in the second list. For iCtr = 1 To iListCount ' Do comparison of next record. ' To specify a different column, change 1 to the column number. If x.Value = Sheets("Master").Cells(iCtr, 1).Value Then ' If match is true then delete row. Sheets("Master").Cells(iCtr, 1).EntireRow.Delete xlShiftUp ' Increment counter to account for deleted row. iCtr = iCtr + 1 End If Next iCtr Next Application.ScreenUpdating = True MsgBox "Done!" End Sub ------------------- Sincerely, Leith Ros -- Leith Ros ----------------------------------------------------------------------- Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846 View this thread: http://www.excelforum.com/showthread.php?threadid=56016 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to compare two list
Thanks Leith How difficult is it to rather than delete the row, to cut and paste it onto another workbook? Sorry to trouble you with this, but it would be a great help! Darren -- DGillham ------------------------------------------------------------------------ DGillham's Profile: http://www.excelforum.com/member.php...o&userid=13707 View this thread: http://www.excelforum.com/showthread...hreadid=560165 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare my list to an existing list | Excel Worksheet Functions | |||
Macro to compare two list | Excel Programming | |||
Help,, need macro to replace 'space' in list of names with "." (dot), then compare ss | Excel Programming | |||
Compare List | Excel Worksheet Functions | |||
Compare List | Excel Worksheet Functions |