Create named ranges using the first row

[alice2011]

Hello sir, I'm new to VBA and looking for a quick help.

I want to create named ranges using a VBA. In my Excel file, the first row provides the heading of each column. I want to use these headings to create named ranges for each column in the selected area. A sample file is attached.

1) If the heading of the column is empty, then ignore this column for named range.

2) The range is subject to selection. For example, if I select area of A2:C50, then the range for each column is from row 2 to row 50. In this case range A2:A50 will be named as 'Sales', range B2:B50 has no name (due to empty value of B1), and range C2:C50 will be named as 'Growth'. Of course, if I select area of A2:C100, then the row stops at 100th.

Thank you a lot.

[alice2011]

Just quickly edited it to make my question clearer

[walruseggman]

Not really an elegant solution, but this works.

Please Login or Register  to view this content.

[alice2011]

Hi walruseggman, your macro works like a magic. Really does the job. Thank you so much. It's really useful for my task