Resize range of non-contiguous cells

[Paul Martin]

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

 

[Rick Rothstein]

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.

 

[Bernie Deitrick]

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]

Thanks for the responses; they're both useful.

 

[Rick Rothstein]

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.

 

[Bernie Deitrick]

Rick,

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

HTH,
Bernie
MS Excel MVP

 

[Rick Rothstein]

I realize that. My message was a follow up to my first message where I
said...

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"))

My last posted statement (the one you are referring to) is just a
modification of the above statement with a recognition so the OP can apply a
dynamic resize under the conditions he originally posted... it was not meant
as a general method to resize non-contiguous ranges (for which the overlap
problem I mentioned would have to be addressed).

 

[Paul Martin]

The original range is all in the one column, so Rick's solution is
fine, and I like the modification you suggested. Many thanks.

Paul