typos...


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
v1 = Sheets("Sheet1").Range("C2:C" & lLastRow)

With Sheets("Sheet2")
lLastRow = .Cells(Rows.Count, "H").End(xlUp).Row
v2 = .Range("H2:H" & lLastRow)
For n = lStartRow To UBound(v1)
For j = lStartRow To UBound(v2)
If v1(n, 1) = v2(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

I also meant to mention about empty cells will match so you may want to
check this with an 'And' operator on at least one of the arrays.

--
Garry

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