| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining multiple ranges as one range
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Defining multiple ranges as one range
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. On May 25, 9:57*pm, "Don Guillett" wrote: Not quite sure of what you want but try this idea range("a3, z3,c4,a4:b12") -- Don Guillett Microsoft MVP Excel SalesAid Software "yeekiat" wrote in message ... 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- Hide quoted text - - Show quoted text - |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Defining multiple ranges as one range
I have found the solution:
Set GroupedRange = Range("E7", Range("E7").End(xlToRight)) Thanks for the help On May 25, 10:16*pm, yeekiat wrote: 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. On May 25, 9:57*pm, "Don Guillett" wrote: Not quite sure of what you want but try this idea range("a3, z3,c4,a4:b12") -- Don Guillett Microsoft MVP Excel SalesAid Software "yeekiat" wrote in message .... 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- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Defining multiple ranges as one range
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 "yeekiat" wrote in message ... I have found the solution: Set GroupedRange = Range("E7", Range("E7").End(xlToRight)) Thanks for the help On May 25, 10:16 pm, yeekiat wrote: 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. On May 25, 9:57 pm, "Don Guillett" wrote: Not quite sure of what you want but try this idea range("a3, z3,c4,a4:b12") -- Don Guillett Microsoft MVP Excel SalesAid Software "yeekiat" wrote in message ... 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- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Defining Multiple "named" data ranges for Piot Tables in Excel 200 | Excel Discussion (Misc queries) | |||
| Defining Ranges | Excel Worksheet Functions | |||
| Automatically Defining Ranges | Excel Programming | |||
| Re-defining Ranges in VBA | Excel Programming | |||
| Defining Ranges | Excel Programming | |||
Hybrid Mode
