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!!

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!!

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!!