Macro: selecting ranges from list of names

A

Antman

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

Rick Rothstein

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
 
J

Joel

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top