how to see all named ranges

[bigdan]

I've come across the Name Manager function. It's pretty decent. However one doesn't get a visual of which areas of the workbook are named. Is there some way to get those parts of the workbook highlighted?

 

[HalfAce]

Hi bigdan,

Is there some way to get those parts of the workbook highlighted?

You mean besides clicking the down arrow at the left end of the formula bar and choosing one of the named ranges that displays?

Or... are you looking for some code that will list all the named ranges and their range addresses?

Does that help?
(Regular) Dan

 

[bigdan]

You mean besides clicking the down arrow at the left end of the formula bar and choosing one of the named ranges that displays?

lol!! that was easy! i had no idea about that one. that's not exactly what i was looking for but it'll solve the problem as well.

however in case this can be made more exact, i was looking for something that would highlight all the named ranges at the same time. can this be done? if not the given solution will work fine.

thanks dan!

 

[Ruddles]

Save your workbook before running this!

[FONT=Fixedsys]Option Explicit[/FONT]
 
[FONT=Fixedsys]Public Sub HighlightNamedRanges()[/FONT]
 
[FONT=Fixedsys]  Dim oName As Name[/FONT]
[FONT=Fixedsys]  Dim mySheet As String[/FONT]
[FONT=Fixedsys]  Dim myRange As String[/FONT]
[FONT=Fixedsys]  Dim oCell As Range[/FONT]
 
[FONT=Fixedsys]  For Each oName In ThisWorkbook.Names[/FONT]
[FONT=Fixedsys]    If InStr(oName.RefersTo, "#REF!") = 0 Then[/FONT]
[FONT=Fixedsys]      mySheet = Mid(oName.RefersTo, 2, InStr(oName.RefersTo, "!") - 2)[/FONT]
[FONT=Fixedsys]      myRange = Mid(oName.RefersTo, InStr(oName.RefersTo, "!") + 1)[/FONT]
[FONT=Fixedsys]      Sheets(mySheet).Range(myRange).Interior.Color = vbYellow[/FONT]
[FONT=Fixedsys]    End If[/FONT]
[FONT=Fixedsys]  Next oName[/FONT]
 
[FONT=Fixedsys] MsgBox "WARNING: DON'T SAVE THIS WORKBOOK!" & Space(10), vbOKOnly + vbExclamation[/FONT]
 
[FONT=Fixedsys]End Sub[/FONT]

 

[Ruddles]

Run it a second time with Interior.Color = xlNone to clear the highlighting. (This won't restore whatever colour was in there to start with, which is why I recommended saving the workbook first!)


You could add these lines immediately before the End If and the named ranges will then appear in a message box with the highlighting taking place as you click OK:-

[FONT=Fixedsys]      Sheets(mySheet).Activate
      MsgBox oName.Name & " " & oName.RefersTo
[/FONT]

 

[bigdan]

wow thanks a lot for this!

i dont actually know how to code yet so i wont be able to do this just yet. however that's next on my todo list so who knows, maybe even today.

so i take it since there was a need for this code excel doesnt have a built-in function for this. funny i think that'd be an imp thing. oh well.

thanks!

 

[RoryA]

Wouldn't oName.ReferstoRange be easier?

 

[Ruddles]

There are plenty of instructions on how to run VBA code but if ou get stuck, jst ask here and someone will respond.

There are all sprts of things which Excel won't do and VBA fills the gaps. There are also loads of things Excel will do but you decide you want them done slightly differently, so again VBA is called in to play.