Rick,

Your method works only if the original ranges are all in the same column.

HTH,
Bernie
MS Excel MVP


"Rick Rothstein" wrote in message
...
Actually, in thinking about it some more, for the approach I proposed, you would probably want to
implement it this way...

Set rngArea = Intersect(Range("A1:A4, A6:A7, A10:A15").EntireRow, Columns("A").Resize(,2))

That way, you could replace the 2 in the Resize property at the end of the statement with a
variable and then dynamically expand the non-contiguous range with a call to the Resize property
similar to the way you originally tried. I would probably generalize this further by Set'ting the
non-contiguous range to a Range variable and then using that in the above Set statement. Something
like this...

Set NonContigRange = Range("A1:A4, A6:A7, A10:A15")
ResizeAmount = 2
Set rngArea = Intersect(NonContigRange.EntireRow, Columns( _
NonContigRange.Column).Resize(, ResizeAmount))

which I think would give you the most flexibility in the end.

--
Rick (MVP - Excel)


"Paul Martin" wrote in message
...
Thanks for the responses; they're both useful.