A client asked for my help on this problem. How to create a Named Range in Excel for Non-Contiguous Cells. The problem is that Excel puts a 255 character limit on the “Refers to” portion of a Named Range. I struggled with this until I found the answer in one of my favorite resource books, “This isn’t s Excel, it’s Magic!” written by Microsoft MVP (Most Valued Professional) Bob Umlas.
Solve the Problem
- Select the non-contiguous cells that you want to name.
- Open up the Visual Basic Environment (Alt + F11 is a Keyboard Shortcut).
- In the VBE, open up the “Immediate Pane” – Ctrl + G is the Keyboard Shortcut.
- Finally, type Selection.Name = “MyLabels” in the Immediate Pane and press Enter. (Substitute the name you want for “MyLabels”).
When you return to Excel, you will see that you can use the Drop-down list in the Name Box or in the Excel 2007 Name Manager to select this range.
Amazing! Thanks Bob!
Additional tips
- Keyboard Shortcut F5 to open up the “Go To” Dialog Box. Select “Special” and choose “Constants – Text” to select the non-contiguous Labels that you want to name.
- Right-click the worksheet tab and select “View Code” top open up the VBE (Visual Basic Environment) window.
I invite you to watch and subscribe to my Video Podcast on iTunes – follow this link to “Danny Rocks Tips and Timesavers”
Learn How to “Master Excel in Minutes – Not Months!”
How to Name a Large Range of Non-contiguous Cells in Excel [ 4:49 ] Play Now | Play in Popup | Download (788)Link to this post!
HI…oh
this is great…,i have learnt much .
thanks alot….,i’m always online .
Hi Danny,
very useful. I am facing an issue trying to use a non contiguous named ranges within an excel function call (not VBA). It returns #Value. Any Clue how to workaround ?
Thanks, Lionel.
Hello Lionel – Thanks for adding your comment. I am pleased that you enjoyed my video.
Re: You problem. First, I suggest that you follow the steps that I demonstrated in the video to create a named range for your non-contiguous cell range.
So that I might be able to further help you, what Excel Function are you trying to use?
Danny Rocks
The Company Rocks
Thank you Danny for your quick follow up.
I created a names ranges Rng from 10 non contiguous ranges rng1, rng2,……,rng10 following your suggestion above. So far everything works fine.
Now I have a customize ( not excel built in ) function func(…), where I pass tha range object Rng as func( Rng). It does work, however when I pass a contiguous range like func(rng1), it works fine. My guess is that they might an issue with an offsetting limitation that prevent non contiguous named ranges to be passed to function.
Any clue ?
Thanks, Lionel.
I meant it doesn’t work when I do the following call: func(Rng) where Rng is the non contiguous named range, but I does work if Rng was referring to a contiguous named range.
Thanks, Lionel
Hello Lionel –
Inside your User Defined Function (UDF) – Which Excel Function are you trying to use? Your answer will help to point you in the proper direction for your answer.
Danny Rocks
The Company Rocks
It doesn’t even hit the first statement inside the UDF. The problem is that when I use the fx menu in the formula tab, the non contiguous range appears as #Value, meaning that it is not resolved.
Thanks, Lionel
Hi Chris – Sorry for the delay in responding. I must admit that without looking at your worksheet, I cannot offer much help with this problem.
Danny Rocks
The Company Rocks