Create Excel Dynamic Named Range using VBA

is there a way to create excel dynamic named ranges using VBA that in one go, for all active worksheets in a workbook dynamic named ranges for columns based on the top row header are created?

thanks.
LVL 27
ProfessorJimJamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Glenn RayExcel VBA DeveloperCommented:
Do you want to create named ranges for individual columns so that the header row label is also the name of the range?  If so, you might want a macro that creates Excel Tables which, as a result, would make them dynamically-sized.  

I presume that every sheet that has a value in row 1 of each column means that a range name is to be created.  Please confirm.

-Glenn
0
Glenn RayExcel VBA DeveloperCommented:
Here is example code that would achieve what I described above.
Sub Create_RangeNames()
    Dim wbk As Workbook
    Dim sht As Worksheet
    Dim rng, rng2 As Range
    Dim cl As Object
    Dim c As Long
    
    Set wbk = ActiveWorkbook
    For Each sht In wbk.Sheets
        c = sht.Cells.SpecialCells(xlLastCell).Column
        Set rng = sht.Range("A1", sht.Range("A1").Offset(0, c))
        For Each cl In rng
            If cl.Value <> "" Then
                Set rng2 = sht.Range(cl, cl.End(xlDown))
                sht.ListObjects.Add(xlSrcRange, rng2, , xlYes).Name = cl.Value
            End If
        Next cl
    Next sht
End Sub

Open in new window


It actually creates Excel Tables and sets their name to be equal to the header row value.  The attached example workbook demonstrates this.

Regards,
-Glenn
EE-CreateTables.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ProfessorJimJamAuthor Commented:
thank you Glenn. I appreciate your help.

but what I was looking was the named ranges. not excel table.

here is complete detail of what I need.
http://www.mrexcel.com/forum/excel-questions/805529-urgent-help-needed-creating-dynamic-named-ranges-using-visual-basic-applications.html
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ProfessorJimJamAuthor Commented:
if It would not be possible to have this through normal Named Range as I have requested then I would accept your earlier solution.
0
ProfessorJimJamAuthor Commented:
I will accept the table option, as you have spent time writing it for me. but I am initiating a new threat with the specific to the dynamic named range.  and I will put the category under Microsoft Excel Product as you have recommended.
0
Glenn RayExcel VBA DeveloperCommented:
I'll look into your other question and see if I can offer a range-specific solution.

The reason I chose the Excel Table option was because one of the characteristics of Excel Tables is that their size is dynamic:  adding a new row of data automatically expands the range.

Regards,
-Glenn
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.