Hi Howrd,

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

MyArr1 = Sheets("Sheet1").Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row).Value
^^^^^
Cells(Rows.count.... is not correctly refered.
If you start the macro from Sheet3 and Sheet3 is empty this will be 0.
You have to refer this to the expectd sheet:

MyArr1 = Sheets("Sheet1").Range("C2:C" & Sheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row)
or you first calculate the last row:

LRow1 = Sheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row
MyArr1 = Sheets("Sheet1").Range("C2:C" & LRow1)

with my code it would look like:

Sub ColumnsCompare()

Dim i As Long, ii As Long
Dim LRow1 As Long, Lrow2 As Long
Dim MyArr1 As Variant
Dim MyArr2 As Variant
Dim rngBig As Range

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

Application.ScreenUpdating = False

With Sheets("Sheet2")
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 = .Range(.Cells(ii + 1, 1), .Cells(ii + 1, 26))
Else
Set rngBig = Union(rngBig, _
.Range(.Cells(ii + 1, 1), .Cells(ii + 1, 26)))
End If
End If
Next 'ii
Next 'i
End With
If Not rngBig Is Nothing Then
Sheets("Sheet3").Range("A1").Resize(rngBig.Rows.Co unt, _
rngBig.Columns.Count).Value = rngBig.Value
Else
MsgBox "no matches found"
End If

Application.ScreenUpdating = True
End Sub


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