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.
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion