Selecting all named ranges in one go using VBA

Dear experts:

I got a whole bunch of named ranges in my current workbook, named range01, range02, range03, range04, range05 and so forth. These are all workbook level scope ranges.

How can I select these ranges in one go using VBA?

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Andreas HermleTeam leaderAsked:
Who is Participating?
 
kgerbChief EngineerCommented:
Andreas,
Here is the code.  It loops through each cell in each named range.  If an empty cell is found it fills it with two spaces.  Let me know how it works.
Sub SelectAllNamedRangesOneAtATime()
Dim n As Name, r As Range, sht  As String
For Each n In ThisWorkbook.Names
    sht = Replace(Mid(n, 2, InStr(1, n, "!") - 2), "'", "")
    If ActiveSheet.Name <> sht Then Sheets(sht).Activate
    For Each r In Range(n)
        If r = "" Then r = "  "
    Next r
Next n
End Sub

Open in new window

Kyle
0
 
kgerbChief EngineerCommented:
I'm pretty sure you're not going to be able to perform an action on all named range in a single line.  However, you can loop through them and do something to them one at a time.  Here's an example.
Sub SelectAllNamedRangesOneAtATime()
Dim n As Name, sht As String
For Each n In ThisWorkbook.Names
    'Put your code here
    'For example
    sht = Replace(Mid(n, 2, InStr(1, n, "!") - 2), "'", "")
    If ActiveSheet.Name <> sht Then Sheets(sht).Activate
    Range(n).Select
Next n
End Sub

Open in new window

Kyle
0
 
Rory ArchibaldCommented:
Are they all on the same sheet? What do you want to do to them?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Andreas HermleTeam leaderAuthor Commented:
Hi rorya:

they, ie. the ranges are all on the same sheet. I would like to perform a search and replace action after they have all been selected.

Thank you.

HI Kyle:

I understand that I am to complete the code myself with a couple of lines (you wrote: 'Put your code here and 'for example). As a matter of fact I do not know how to complete it.

If I run your code without completing only the last named range of the ranges named range_01, range_02 and so forth gets selected.

Thank you,

Regards, Andreas a
0
 
kgerbChief EngineerCommented:
No problem, I can help you with the code.

You said you need to search for something in each of the named ranges and replace it with something else.  What are you searching for and what do you want to replace it with?

Kyle
0
 
Andreas HermleTeam leaderAuthor Commented:
Kyle:

I am  searching for blank cells in these ranges. The found cells are to be filled with a couple of spaces. May sound odd, but I need all cells filled somehow.

Thank you.

Regards, Andreas
0
 
Andreas HermleTeam leaderAuthor Commented:
I am away on a business trip. Will test it shortly.
0
 
Andreas HermleTeam leaderAuthor Commented:
Hi kgerb,

works just great! Thank you very much for  your professional help. I really appreciate it.

Regards, Andreas
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.