Macro: selecting ranges from list of names

  • Thread starter Thread starter Antman
  • Start date Start date
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!!
 
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
 
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.
 
Back
Top