PC Excel range name scopes: how to edit these?
And how to paste them somewhere useful?
Also is there a way to identify names not in use?
I'd prefer on a Mac, but suspect that's not possible.
Edit: just to be clear I do not mean how to edit these one at a time, I mean in bulk. Even with VBA.
Anthony
Also is there a way to identify names not in use?
I'd prefer on a Mac, but suspect that's not possible.
Edit: just to be clear I do not mean how to edit these one at a time, I mean in bulk. Even with VBA.
Anthony
Last Comment
ASKER
Hi Martin, Yes I am familiar with named ranges.
I envisage PC Excel range name scopes and how to bulk edit these?
So for example:
to set all these to "workbook" scope.
Regards Anthony
I envisage PC Excel range name scopes and how to bulk edit these?
So for example:
to set all these to "workbook" scope.
Regards Anthony
Try this:
Sub MakeGlobal()
Dim nr As Name
Dim strName As String
Dim strRefersTo As String
For Each nr In ActiveWorkbook.Names
If Not nr.RefersToRange Is Nothing Then
With nr.RefersToRange
strName = Split(nr.Name, "!")(1)
strRefersTo = nr.RefersToR1C1
' Remove the named range
ActiveWorkbook.Names(nr.Name).Delete
' Add it back as global
ActiveWorkbook.Names.Add Name:=strName, RefersToR1C1:=strRefersTo
End With
End If
Next nr
End Sub
ASKER
switching to PC .. actually I'll see if that runs on a mac..
ASKER
ok maybe not, Â
OFF TO PC WE GO
OFF TO PC WE GO
Which line gives the error?
ASKER
GREAT, I don't know how to run a macro in pc excel
ASKER
ASKER
is that the right macro? ok yes think it is
ASKER
same error on pc
Change that line to
strName = nr.Name
strName = nr.Name
ASKER
Will do.. need sleep first thanks:-)
ASKER
not yet:
Sub MakeGlobal()
Dim nr As Name
Dim strName As String
Dim strRefersTo As String
For Each nr In ActiveWorkbook.Names
  If Not nr.RefersToRange Is Nothing Then
    With nr.RefersToRange
      strName = nr.Name
      strRefersTo = nr.RefersToR1C1
      ' Remove the named range
      ActiveWorkbook.Names(nr.Name).Delete
      ' Add it back as global
      ActiveWorkbook.Names.Add Name:=strName, RefersToR1C1:=strRefersTo
    End With
  End If
Next nr
End Sub
Sub MakeGlobal()
Dim nr As Name
Dim strName As String
Dim strRefersTo As String
For Each nr In ActiveWorkbook.Names
  If Not nr.RefersToRange Is Nothing Then
    With nr.RefersToRange
      strName = nr.Name
      strRefersTo = nr.RefersToR1C1
      ' Remove the named range
      ActiveWorkbook.Names(nr.Name).Delete
      ' Add it back as global
      ActiveWorkbook.Names.Add Name:=strName, RefersToR1C1:=strRefersTo
    End With
  End If
Next nr
End Sub
It works for me in Excel 2010. Did you test it in Widows?
ASKER
no I'm on Mac
ASKER
doing that now
ASKER
same error on pc
Can you attach the Windows Excel version of your workbook?
ASKER
it's the same file...?
ASKER
file herewith
except had to dlete it a sit's full or private data -Â
except had to dlete it a sit's full or private data -Â
ASKER
I'll see if i can do  quick delete... but it's a vast number of deletions
ASKER
Championship 2021 28MarV50_Bill_02-namesdeleted.xlsx
there ye go, that's from the windows pc thugh why ou might think it shoud be any different is a bit perplexing.
there ye go, that's from the windows pc thugh why ou might think it shoud be any different is a bit perplexing.
ASKER
funnily enough the deletion of names was greatly assisted by most of the entries being derived using Dynamic ArraysÂ
ASKER
can't stay awake any longer, thank for your help Martin, see you later, anthony
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Good morning Martin, delete which/what "ones that are not being used"? - Anthony
p.s. the file you requested is attached above.
p.s. the file you requested is attached above.
I'm sorry but I don't know how to help you.
One problem you have is that there are named ranges like _FilterDatabase that have the same name but refer to different data, so I don't think it's possible to convert them to workbook scope.
One problem you have is that there are named ranges like _FilterDatabase that have the same name but refer to different data, so I don't think it's possible to convert them to workbook scope.
ASKER
Ok that helps, thanks. I'll come back if I can add any more to this.Â
ASKER
Penny drops: you mean delete the unused range names and try running it again. Noted, thanks.
Anthony
Anthony
One thing that may be useful is the Filter button
I’m glad I was able to help and thank you for the testimonial.
If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.
Marty - Microsoft MVP 2009 to 2017
          Experts Exchange Most Valuable Expert (MVE) 2015, 2017
          Experts Exchange Distinguished Expert in Excel 2018
          Experts Exchange Top Expert Visual Basic Classic 2012 to 2020
          Experts Exchange Top Expert VBA 2018 to 2020
If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.
Marty - Microsoft MVP 2009 to 2017
          Experts Exchange Most Valuable Expert (MVE) 2015, 2017
          Experts Exchange Distinguished Expert in Excel 2018
          Experts Exchange Top Expert Visual Basic Classic 2012 to 2020
          Experts Exchange Top Expert VBA 2018 to 2020
ASKER
Martin - you are very welcome and thanks for your time in assisting me. -
I try not to ask easy questions. Anthony
I try not to ask easy questions. Anthony
And I enjoy your offbeat sense of humor.
ASKER
Story of my life! :-)
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
What kind of bulk change do you envision making?