selecting named ranges that include particular text

[keithmct]

I want to select multiple price grids so as to increase them by a few percent if needed. They are all named ranges and all end in "prices". Can I do something in a macro like:
Range(*&"prices").Select ?

 

[Akuini]

some of my named ranges with "prices" in their name are no longer used and I wanted to move them to a new worksheet that I can hide

Union method works only if name ranges are in a single sheet.
If they are no longer used why not just delete them?

 

[keithmct]

If they are no longer used why not just delete them?

because you never know? just in case? it was a lot of work originally and it seems a waste to delete?
I'll move them and just remove the named ranges instead. thanks for the answer though.

 

[Akuini]

Well, there's a way to do it. Let's say the sheet name where you want to process the name ranges is "Sheet1", you can alter your code like this:

Sub Test2()
Dim nm
Dim c As Range

For Each nm In ActiveWorkbook.Names
    If nm.Name Like "*prices" Then
        If Range(nm.Name).Parent.Name = "Sheet1" Then
    '        Debug.Print nm.Name
            If c Is Nothing Then
               Set c = Range(nm.Name)
            Else
               Set c = Union(c, Range(nm.Name))
            End If
        End If
    End If
Next nm

'now, c is holding all those named ranges.
If Not c Is Nothing Then
c.Select
Else
MsgBox "Can't find names that end with 'prices'"
End If

End Sub

So, basically the code says, get me all the name ranges with "prices" in the end and they refer to range in Sheet1.