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.

I normally use a position counter (lNextRow) for this once the 1st
empty row is found using End(xlUp), and increment it each time I write
a new row to the target sheet.

Optionally, if the output is an array then...

wksTarget.Cells(lNextRow, 1).Resize(Ubound(vData), UBound(vData, 2)

...to set height/width of the target area to the y/x size of the array.

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.

You can't get your feet wet until you get in the water!<g Same goes
for learning to swim. Well, you're definitely in the water and so just
need to persist toward your goal!<g

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion