Paul,

Offset works, so you can resize this way:


Sub test()
Dim myR As Range
Set myR = Range("A1:A4, A6:A7, A10:A15")
Set myR = Union(myR, myR.Offset(0, 1))
Msgbox myR.Address
End Sub

To add more than one column, just repeat:

Sub test2()
Dim myR As Range
Dim i As Integer
Set myR = Range("A1:A4, A6:A7, A10:A15")
For i = 1 To 3
Set myR = Union(myR, myR.Offset(0, 1))
Next i
MsgBox myR.Address
End Sub


HTH,
Bernie
MS Excel MVP

"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