Selecting all named ranges in one go using VBA

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

Select allOpen in new window

Kyle

Are they all on the same sheet? What do you want to do to them?

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

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

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

I am away on a business trip. Will test it shortly.

Hi kgerb,

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

Regards, Andreas