On Thursday, February 12, 2015 at 1:10:03 AM UTC-8, GS wrote:
Optionally...

Sub ColumnsCompare2()
Dim n&, j&, lLastRow
Dim v1, v2, rngBig As Range

Const lStartRow& = 2

Application.ScreenUpdating = False
On Error GoTo ErrExit

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

With Sheets("Sheet2")
lLastRow = .Cells(Rows.Count, "H").End(xlUp).Row
MyArr2 = .Range("H2:H" & lLastRow)
For n = lStartRow To UBound(MyArr1)
For j = lStartRow To UBound(MyArr2)
If MyArr1(n, 1) = MyArr2(j, 1) Then
If rngBig Is Nothing Then
Set rngBig = .Range(.Cells(j, 1), .Cells(j, 26))
Else
Set rngBig = Union(rngBig, _
.Range(.Cells(j, 1), .Cells(j, 26)))
End If
End If
Next 'j
Next 'n
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

ErrExit:
Set rngBig = Nothing
Application.ScreenUpdating = True
End Sub

--
Garry


Hi Garry, thanks for weighing in.

Had to Dim the two MyArr1 & MyArr2 and find that the code returns rows 33, 4 & 3 only. Which is strange since 33 is not a match and 3 and 4 are a match and there are several other rows that are a match which did not copy to sheet 3.

The code also writes to A1 each time it is run instead of to next empty cell I column A, sheet3.

The main reason I looked to arrays was my thought of dealing with a very long list on sheets 1 & 2, although my example is pretty small.

As you can see, I am still on the outside looking in when I comes to arrays.

Howard