Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro: selecting ranges from list of names
I have a number of ranges in my workbook that I have named, and a named range
containing the names of each of those ranges. Example: Range1 = A1:B3 Range2 = D1:E3 Range3 = G1:H3 NameList = Range1, Range2, Range3 I want to apply a short block of macro code to each Range1, Range2, Range3... in succession, without naming the ranges specifically in the macro code. Ideally, I want to be able to add new ranges over time and have the macro apply to them automatically once I add the range name to NameList. For example, from the above, I want to add the name Range4 = I1:I3, add Range4 to NameList, and then have the macro work on Range4 as well as Range1...Range3. I was thinking of using a CHOOSE() functoin on NameList, but I'm not sure how this might work in a macro, or how I could put in a formula result rather than a specific name in my macro (I'm good with formulas, but terrible with macros). Thanks in advance!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro: selecting ranges from list of names
This should get you started...
Sub ProcessNames() Dim N As Name For Each N In ActiveWorkbook.Names ' Replace the sample Debug.Print statement with your own code Debug.Print Range(N).Address Next End Sub -- Rick (MVP - Excel) "Antman" wrote in message ... I have a number of ranges in my workbook that I have named, and a named range containing the names of each of those ranges. Example: Range1 = A1:B3 Range2 = D1:E3 Range3 = G1:H3 NameList = Range1, Range2, Range3 I want to apply a short block of macro code to each Range1, Range2, Range3... in succession, without naming the ranges specifically in the macro code. Ideally, I want to be able to add new ranges over time and have the macro apply to them automatically once I add the range name to NameList. For example, from the above, I want to add the name Range4 = I1:I3, add Range4 to NameList, and then have the macro work on Range4 as well as Range1...Range3. I was thinking of using a CHOOSE() functoin on NameList, but I'm not sure how this might work in a macro, or how I could put in a formula result rather than a specific name in my macro (I'm good with formulas, but terrible with macros). Thanks in advance!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro: selecting ranges from list of names
You have three choices.
1) Aopply the macro to all the Named Ranges. Add Named ranes in Worksheet menu Insert - Name - Define. Then have the macro get each of the named ranges 2) Have a list of the Named Ranges on a worksheet starting at a specific location with each range on its own row.. Then continue down the worksheet until you get to a blank cell 3) Rather than use named ranges just put range address in the workbook like A1 - B5:B10 A2 - C20:D25 The macro can get each range until it find an empty cell. "Antman" wrote: I have a number of ranges in my workbook that I have named, and a named range containing the names of each of those ranges. Example: Range1 = A1:B3 Range2 = D1:E3 Range3 = G1:H3 NameList = Range1, Range2, Range3 I want to apply a short block of macro code to each Range1, Range2, Range3... in succession, without naming the ranges specifically in the macro code. Ideally, I want to be able to add new ranges over time and have the macro apply to them automatically once I add the range name to NameList. For example, from the above, I want to add the name Range4 = I1:I3, add Range4 to NameList, and then have the macro work on Range4 as well as Range1...Range3. I was thinking of using a CHOOSE() functoin on NameList, but I'm not sure how this might work in a macro, or how I could put in a formula result rather than a specific name in my macro (I'm good with formulas, but terrible with macros). Thanks in advance!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting tabs - tab names given in a list | Excel Programming | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
Macro to make a graph: help selecting the data ranges. | Excel Programming | |||
Using names ranges to set print area in a macro | Excel Programming | |||
Excel macro to insert names for ranges, not always the same size.. | Excel Programming |