Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize range of non-contiguous cells
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize range of non-contiguous cells
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize range of non-contiguous cells
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize range of non-contiguous cells
Thanks for the responses; they're both useful.
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize range of non-contiguous cells
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize range of non-contiguous cells
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize range of non-contiguous cells
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). -- Rick (MVP - Excel) "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Resize range of non-contiguous cells
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range and non contiguous cells | Excel Programming | |||
Add non-contiguous cells to range? | Excel Programming | |||
counting cells that are 0 in a range of non-contiguous cells | Excel Worksheet Functions | |||
pasting non-contiguous range of cells to new row, same cell locati | New Users to Excel | |||
Range holding non-contiguous cells | Excel Programming |