Automatically Create Named Range based on a cell label

[Vamphyri]

Yes, it's me again.

I've searched a ton on this subject, but can't seeem to find what I'm looking for.

I am trying to have Excel automatically create named ranges based on the entry within a certain cell. For instance:

- Have the name to be assign to the range be in row 1.
- Data will be in 3 columns per named range.
- Each Named range will be 3 columns wide by 45 rows long.

Is there a way to have VBA look in cell (A1 or D1 or G1, etc) and assign the whatever is written in that cell as a named range for the 3 columns X 45 Rows dynamically?

I'm trying to create a source file to hold all products and their particulars that I want to bring into a comparison (separate workbook). The products and specifications are ALWAYS changing, so a static list will not work.

Any help on this would be greatly appreciated.

REMEMBER: Only 251 shopping days til Christmas (yeah, I know... WHATEVER!!!)




In the immortal words of Socrates, who said:
"I drank what?

 

[SkipVought]

Hi,

SELECT a range including the Names you want for the Row/Column

Insert/Name/Create - Create name in...

Top
Left
Right
Bottom

Check one or two

VOLA!

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Vamphyri]

Skip,

Thanks for the tip!

However, sometimes the middle of the 3 columns is blank. I think this is causing an error. When I use you tip it sets the range for only the first column. Is there a way to "force" the named range to be 3 columns by 45 rows and still apply whatever is in row 1 as the name?

Thanks

Whenever I feel the need to take off all of my clothes and go jogging I just take a drink of Windex... It prevents streaking!


In the immortal words of Socrates, who said:
"I drank what?

 

[SkipVought]

Not using the Create feature.

You can use Insert/Name/Define...

but it takes typing the Range Name and the Refers To expression...

How can I rename a table as it changes size faq68-1331

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[KenWright]

No problems for me doing it that way as long as the whole range was selected, or perhaps using code:-

Sub MakeNames()

Set rng = Range("C1:E45")
    With rng
        .CreateNames Top:=True, Left:=False, Bottom:=False, Right:=False
    End With
End Sub

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

Ken,

The Name in C1 only refers to C2:C45

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[KenWright]

Oops - forget that - just saw I think that you only want one name for the 3x45 range. insert / Name create will give you 3 2 or 1 depending on data.

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

LOL - just twigged

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

here's some code.

Select the range, INCLUDING the name in the upper lh cell of the selection

    With Selection
        ActiveWorkbook.Names.Add _
        Name:=.Cells(1, 1), RefersTo:="='" & ActiveSheet.Name & "'!" & Range(.Cells(2, 1), .Cells(.Rows.Count, .Columns.Count)).Address
    End With

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[KenWright]

And just for the hell of it, if you intend to name umpteen ranges across the worksheet, eg A2:C45, D2:F45, E2:G45 etc, then perhaps

Sub MakeNames()

Dim namrng As Range
    For c = 1 To 255 Step 3

     Set namrng = Cells(1, c)
        With namrng
            If namrng <> "" Then
                namval = .Value
                Set rng = Range(Cells(2, c), Cells(45, c + 2))
                ActiveWorkbook.Names.Add Name:=namval, RefersTo:=rng
            End If
        End With
    Next c
End Sub

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Vamphyri]

Ken,

That worked beautifully!. Now, how can I make the same code do the same thing for all worksheets in a workbook?

I think I would use a LOOP, but I must be doing something wrong. I keep getting Error 400 (like I know hwat that is).

Any ideas?

The end result of this will be to have the named ranges as a Dependent List in a Drop down box (Worksheet names would be the parent). Upon selection of the Named Range i will need to copy the named range to a separate workbook.

Am I going about all this in a very convoluted way?


In the immortal words of Socrates, who said:
"I drank what?