In the line before your loops, replace MyArr2 with a ref to rngSource
so you're working directly on the range itself rather than its data
array...

Set rngSource = Sheets("Sheet2").Range("H2:H" & lLastRow)

..then in your loops, replace UBound(MyArr2) with
rngSource.Rows.Count. Lose the rngBig and Union code and just copy
the rngSource row to Sheet3 right in the loop while matches are
found. This approach may be a tad slower but it saves a 2nd step to
copy the formatting. IOW, you insert each row when matching, NOT
build an array because arrays only handle values NOT formatting.

I forgot to mention your ref to rngBig under the ErrExit label should
now ref rngSource.

--
Garry

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