I would think Resize is restrict on non-contiguous ranges because of the
possibility that expanded areas might overlap (remember, the property must
be able to work in the general case). Now, since what you want to do is
pretty well controlled, you can do it without a loop like this...

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

This will produce the range you are looking for. If you need more columns,
just change the "A:B" in the last Range property.

--
Rick (MVP - Excel)


"Paul Martin" wrote in message
...
Hi guys

I have a non-contiguous range and would like to resize it. Resize
fails and I'm wondering if there is any way other than looping through
each area and resizing the area - which works fine, but I was hoping
for something more efficient (like the use of Resize in a single line,
without looping). Any thoughts?

FWIW, as I loop through each area, I use Union to redefine the
original range. If I start off with say A1:A4, A6:A7, A10:A15, I end
up with A1:B4, A6:B7, A10:B15.

And this is my code:

For Each rngArea In rngParent.Areas
Set rngArea = rngArea.Resize(, 2)
Set rngParent = Union(rngArea, rngParent)
Next rngArea

Thanks in advance

Paul Martin
Melbourne, Australia