Dynamic Named Ranges

Johnny5 · Apr 20th 2004

I am trying to name ranges in a spreadsheet that spans 2 columns (A and B). The data contained is a text values in cell A and numberic values in B. There are 15 sections of data separated by a blank line. I want to use the offset function to name my ranges based off of:

1) The header of the section (Anchor Cell)
eg: if cell a# = Fullname this is anchor

2) Select cells from the cell after the anchor cell down until the next zero value in cell B

I eventually want to make this a VB macro. I want to be able to click a button to search for the headers and name the ranges accordingly.

Here is my named range I created, but it goes all the way down to the end of the spreadsheet instead of stopping at the first zero value in col B

=OFFSET(A!$A$2:$B$2,0,0,COUNT(A!$B:$B))

All help is greatly appreciated!

daniB · Apr 20th 2004

Im really bad with lots of words, i think understand what u are getting at - i have something similar - any chance of an example of the spreadsheet?

I think what u are getting at is that your anchor cell is variable?

Anonymous · Apr 20th 2004

A complex formula could be constructed for each range, but if you are going to use a macro to define the names anyway, why not avoid the pain of constructing the formulas and just let your macro simply cycle through defining the ranges as it comes to them? What needs to be dynamic?

Anonymous · Apr 20th 2004

Here is an example of a complex formula for a defined name. It assumes no more than 500 rows to a name (change the 500 as needed) and that the blank cell delimiters in column A have been replaced by a single space (key assumption).
=OFFSET(A1,MATCH("fullname",A:A,0),0,MATCH(" ",OFFSET(A1,MATCH("fullname",A:A,0),0,500,1),0)-1,2)

Johnny5 · Apr 20th 2004

I would love to be able to just use a vb macro to name the ranges. I thought that doing it in Excel would help me write the macro. I don't write them very often. This spreadsheet is created every week and the ranges will be different. I want to name the ranges so I can insert them into an Excel template. If there is a good way to bypass the formula part and get right to the macro, I would like to hear your idea in a little more detail and try it that way.

Here is an example of what it looks like:

STATE FREQUENCY
CA 334781

ZIP FREQUENCY
9 334781

SCOREDTE FREQUENCY
20040301 334781

MAILDTE FREQUENCY
20040422 334781

PRINTKEY FREQUENCY
126TW19 167390
146TW19 167391

Anonymous · Apr 20th 2004

Try the following macro. It works on your example.

Code

Sub BuildNames()
    Dim i As Long, r As Range, s As String
    i = 1
    While Not IsEmpty(Cells(i, 1))
        s = Cells(i, 1) 'the name
        i = i + 1
        Set r = Cells(i, 1) 'start of range
        While Not IsEmpty(Cells(i, 1))
            i = i + 1
        Wend
        Range(r, Cells(i - 1, 2)).Name = s 'define the range
        i = i + 1
    Wend
End Sub

Display More

Johnny5 · Apr 23rd 2004

Thank you so much.

I am now trying to paste those ranges into a template. I thought I could just say it equals the range on another spreadsheet, but it is only putting in one value.

How can I insert a range into A1 and B1? I need the cells the move down for the first insertion, then just paste into the next cells. Here is the format:

Fname Freq Fullname Freq StName Freq
--------------- ----------------- ---------------
a---------123 a---------- 258 a---------236
b---------321 b-----------852 b---------632

Thanks again

Anonymous · Apr 23rd 2004

Since the name covers more than one cell you will need to enter it as an array. Select the cells where you want the data for a name to appear, then type the reference and hold down the control and shift keys when you hit the Enter key. One problem may be that you won't know how many rows your name needs. Or you could modify the macro to write the appropriate cell reference to your template rather than use defined names. That might be a better solution.

Dynamic Named Ranges

Participate now!

Share