Defining multiple ranges as one range

[yeekiat]

Hi,

I'm new here. cheers to anyone who can help me.

Is there a way for me to define multiple ranges as one range in vba?
The trick to this is that I am not sure how many of them are to be
grouped together as it is dyanamic and changes all the time.

For example. Lets say the ranges E7 to G7 are to be grouped together.
I wish to group all of them as one cell and give it a name such as
GroupedCells, so that I can run codes using Range
("GroupedCells").Value = blablabla.

I know some would suggest that I use Range("GroupedCells") = Range
("E7:G7") but that is not possible because sometimes it could be F7,
G7, H7, I7, etc. depending on whether that particular range is filled.

If it helps I do know how many ranges I'm expected to group. So if it
helps I know that for the case of E7 to G7, the number of cells is 3.

I'll be thankful if anyone can help me out.

Regards,
Yee Kiat

 

[Don Guillett]

Not quite sure of what you want but try this idea

range("a3, z3,c4,a4:b12")

 

[yeekiat]

Basically I need to group a bunch of cells together and give them a
name called GroupedCells.

Normally you would do this by doing:

Range("GroupedCells") = Range("A1:C1")

This is all well and good if you know the last cell that you want to
group together is C1. However I do now know that. As such I have to
write a code that will help me determine the end cell, possibly using
IsEmpty or whatever to find out the end cell. It helps that I know the
number of cells to be grouped together. So for the case of A1:C1, it
is 3. and for the case of A1:B1 it is 2. How do I make use of that
knowledge to find the end cell from A1 (they are all in the same row)
and how do I group them together as Range("GroupedCells")?

Thanks.

 

[yeekiat]

I have found the solution:

Set GroupedRange = Range("E7", Range("E7").End(xlToRight))

Thanks for the help

 

[Peter T]

For that you could use a dynamic name, no code need to update to update

=OFFSET(Sheet1!$E$7,0,0,1,COUNTA(Sheet1!$E$7:$IV$7))

Ensure there are no empty cells in the range (other than those to the right
of the last used cell)

Change $IV$7 for Excel 2007, or better still reduce to the left to the last
cell you imagine might ever need to be included.

Regards,
Peter T


I have found the solution:

Set GroupedRange = Range("E7", Range("E7").End(xlToRight))

Thanks for the help