How to Name a Large Range of Non-Contiguous Cells in Excel
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 (328)
December 2nd, 2010 at 1:52 PM
[...] This post was mentioned on Twitter by Danny Rocks, Danny Rocks. Danny Rocks said: Danny's Latest Post How to Name a Large Range of Non-Contiguous Cells in Excel http://goo.gl/fb/trVu6 #exceltraining [...]
November 29th, 2011 at 10:29 PM
HI…oh
this is great…,i have learnt much .
thanks alot….,i’m always online .