My Never ending ARRAY code problems
Hi Howard,
Am Thu, 12 Feb 2015 02:05:14 -0800 (PST) schrieb L. Howard:
Claus, this ColumnsCompare3() code seems to do well, except it writes to A1 on sheet 3 each time.
try:
Sub ColumnsCompare3()
Dim i As Long, n As Long
Dim LRow As Long, LRow2 As Long
Dim MyArr As Variant
LRow2 = Sheets("Sheet2").Cells(Rows.Count, "H").End(xlUp).Row
MyArr = Sheets("Sheet2").Range("H2:H" & LRow2)
Application.ScreenUpdating = False
Sheets("Sheet2").Range("A2:Z" & LRow2).Copy
Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp)(2) _
.PasteSpecial xlPasteValues
With WorksheetFunction
LRow = Sheets("Sheet3").Cells(Rows.Count, "H").End(xlUp).Row
For i = LBound(MyArr) To UBound(MyArr)
If .CountIf(Sheets("Sheet1").Range("C:C"), MyArr(i, 1)) = 0 Then
n = .Match(MyArr(i, 1), Sheets("Sheet3").Range("H1:H" & LRow), 0)
Sheets("Sheet3").Rows(n).Delete
End If
Next
End With
Application.ScreenUpdating = True
End Sub
Do you think the delete the non-match is ok on much larger columns , say 1500 to 3000 rows on sheet1 and sheet2?
Test it with larger columns. With less than 100 rows the code is faster
than the other suggestions.
Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
|