Hi Howard,

Am Wed, 11 Feb 2015 17:00:22 -0800 (PST) schrieb L. Howard:

Want to compare sheet1 column C list to sheet2 column H list and if match copy column H row A to Z to Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp)(2).

Errors out on the varOut = MyArr2(ii).Offset(, -7).Resize(1, 26).

I am aware that MyArr2 only sees the range on sheet2 as cells H1:H2, while MyArr1 shows 10 elements...??? So for sure the code is going to fail on that alone, but this is my shot at it.

Here is what the compare columns hold on sheet1 & sheet2. So the only sheet2 rows A to Z that would NOT get copied to sheet3 are 33 and 44. No match.

better use a range for outputthan an array.
Try:

Sub ColumnsCompare()

Dim i As Long, ii As Long, n As Long

Dim MyArr1 As Variant
Dim MyArr2 As Variant
Dim rngBig As Range

MyArr1 = Sheets("Sheet1").Range("C2:C" & _
Cells(Rows.Count, "C").End(xlUp).Row).Value
MyArr2 = Sheets("Sheet2").Range("H2:H" & _
Cells(Rows.Count, "H").End(xlUp).Row).Value

Application.ScreenUpdating = False

For i = LBound(MyArr1) To UBound(MyArr1)
For ii = LBound(MyArr2) To UBound(MyArr2)
If MyArr1(i, 1) = MyArr2(ii, 1) Then
If rngBig Is Nothing Then
Set rngBig = Sheets("Sheet2").Range _
(Cells(ii + 1, 1), Cells(ii + 1, 26))
Else
Set rngBig = Union(rngBig, Sheets("Sheet2") _
.Range(Cells(ii + 1, 1), Cells(ii + 1, 26)))
End If
End If
Next 'ii
Next 'i
Sheets("Sheet3").Range("A1").Resize(rngBig.Rows.Co unt, _
rngBig.Columns.Count).Value = rngBig.Value

Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional