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]

Maybe something like this:

Sub Test1()
Dim nm
Dim c As Range
For Each nm In ThisWorkbook.Names
    If nm.Name Like "*prices" 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
Next nm

'now, c is holding all those named ranges.
c.Select

End Sub

 

[keithmct]

excellent. thanks for your help.

 

[keithmct]

tried putting the macro in my custom ribbon tab and now shows runtime error 91 Object variable or With block variable not set at line
c.select

 

[Akuini]

tried putting the macro in my custom ribbon tab and now shows runtime error 91 Object variable or With block variable not set at line
c.select

Where do you put the code? in Personal.xlsb?

 

[keithmct]

I tested it out in a normal active-x button on the relevant sheet which worked perfectly. Tried to customise ribbon to add macro but showing only macros, it wasn't available to add to Main Tab so I then copied it to a Personal.xlsb module, and that seemed to work except for the above error.

 

[Akuini]

Try using ActiveWorkbook.Names instead of ThisWorkbook.Names

Sub Test2()
Dim nm
Dim c As Range
For Each nm In ActiveWorkbook.Names
    If nm.Name Like "*prices" 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
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 'price'"
End If

End Sub

or try using your workbook name:

For Each nm In Workbooks("something").Names

 

[keithmct]

ActiveWorkbook.Names worked, thanks heaps.

 

[Akuini]

you're welcome, glad to help & thanks for the feedback.

 

[keithmct]

further to the above, 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. Now when I use macro in post #7 above I get error:
Run-time error '1004':
Method 'Range' of object'_Worksheet' failed

and upon debug the line
Set c = Union(c, Range(nm.Name))
is highlighted in yellow. Any suggestions?

Private Sub selectprices_Click()
Dim nm
Dim c As Range
For Each nm In ActiveWorkbook.Names
    If nm.Name Like "*prices" 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
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