Sorry about that. Column B of worksheet 1 contains unique values, and Column
A of worksheet 2 contains unique values. I'm wanting to take worksheet 1,
delete any rows that have a match in worksheet 2, and put it into a new
worksheet in the same workbook. The worksheet will have to be added. Below is
what I have, and it works but is taking a very long time. The number of rows
vary, so I put in what would probably be the max number for each worksheet. I
realize there's a way to make it go to the last row of data, but I can't get
that to work. I appreciate your help.
Sub VINComparison()
Application.ScreenUpdating = False
Sheets("Mine").Select
Sheets("Mine").Copy After:=Sheets(2)
Sheets("Mine (2)").Select
Sheets("Mine (2)").Name = "New"
Range("A1").Select
Sheets("Ford").Select
Range("A1:G20000").Select
ActiveWorkbook.Names.Add Name:="FORD", RefersToR1C1:="=Ford!R1C1:
R20000C7"
Range("A1").Select
Sheets("New").Select
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=IF(VLOOKUP(RC[-8],FORD,1,FALSE)=TRUE,"" "",""Retain"")"
Range("J2").Select
Selection.AutoFill Destination:=Range("J2:J3500"), Type:=xlFillDefault
VinComparisonCleanup
End Sub
Sub VinComparisonCleanup()
Range("J2:J3500").Select
Selection.SpecialCells(xlCellTypeFormulas, 16).Select
Selection.EntireRow.Delete
Application.ScreenUpdating = True
End Sub
JLGWhiz wrote:
You objective is not clear.
1. Do you have duplicates in Column B of worksheet 1?
2. Do you have duplicates in Column A of worksheet 2?
3. If yes to 1 & 2 above, do you want to delete the entire row for each
duplicate found?
4. Assuming you mean by "output" that you want the duplicate items recorded
on a third worksheet, do you want the entrie row for each item or just the
data from the duplicate cell of column B or A respectively?
5. Is Sheet3 in the workbook empty or will a new sheet be needed as the
third sheet in this exercise?
I would imagine this is in here somewhere, but I haven't found it, and
can't
[quoted text clipped - 9 lines]
since
I'd need to put it back into Excel anyway. Thanks again.
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/201004/1