name dynamic ranges with VBA

For those times when I have many range names to create...perhaps in
several existing workbooks...I use this VBA code in my Personal.XLS workbook
(but you could put it in any workbook):

Copy/Paste the below code into a Genaral Module.

Please Login or Register  to view this content.

Here's an example of how I'd use it....
First, I create a 2-column list, comprising of:
-Name to be created
-Refers to expression (formatted as text)

Example:
G1: MyDynRangeName
H1: '=OFFSET($C$3,,,COUNTA($C:$C),3)

Select G1:H1
[ALT]+[F8]...a shortcut for <tools><macro><macros>
Select: CreateRangesFromList
Click [Run]

The code will create or rebuild the range names in the list.

In the above example, a dynamic range name is created that
refers to C3:D3 and down for the number of non-blank cells in Col_C.

Is that something you can work with?

Originally Posted by robotlust

Hi,
I've managed to name some dynamic ranges with OFFEST and COUNTA. However I'd really like to manage my ranges with a VBA procedure as there'll be quite a few ranges and the UI method is loathsome.

I found this during some searching but it's not working.

Please Login or Register  to view this content.

I have very limited VBA knowledge. (i can do cut, paste & tweak but cant write yet)

Can anyone assist?

Good morning,
the code works for me,
Did you run the code so it can name the range?

If you want dynamic ranges the fast way, check out this site
http://blog.livedoor.jp/andrewe/archives/50353713.html
select a cell
run the macro,
the code will name a dynamic range from the selected cell down

go to insert>name>define, you will find your dynamic named range there

Hi, Cameron

If you select the 2-column range of cells....
Example:

Please Login or Register  to view this content.

Then....run the macro one time:
[ALT-[F8]....Select: CreateRangesFromList....Click: Run

Those range names will be created.

What it WON'T do is constantly update Range Names
if the formulas in the above cells change.

Does that help?
Post back if you have more questions.