Hi all,

I've got a repitition problem. I have multiple data ranges in one worksheet where I need to create a named range for each. Example:

-------A
1----Name
2-----a
3-----b
4-----c
5-----
6-----

This continues for quite a lot of columns across the worksheet. I know that to do this once I would highlight cells A1:A6 and create a name on the formula tab, create from selection, top row. I also know that I could just highlight cells A1:ZZ6 and create from selection and it would do that too.

My other problem is that not every column will contain 6 values so will be blank which I wouldn't want appearing in Data Validation lists. And again, I know that if I was only doing this once I would use the name manager and edit the range using the offset formula. But, as you can imangine I don't want to be doing this for hundred of named ranges.

Does anybody know a way how I can combine these methods but also to do it automatically rather than having to repeat this process for every column.

Thanks in advance,

Jason