I have a subset of the following named ranges in several different
worksheets:

A_1, A_2, ..., A_20, B_1, B_2, ..., B_20, C_1, C_2, ..., C_20

Is there a way I can get a collection of those that are available for a
particular sheet using wildcards? For example, something like:

for each oRange in WorkSheets("XXX").Range("A_*")
....processing...
next oRange

Something like this will do it:

Function GetWBNames(Optional strStartsWith As String) As Collection

Dim oName As Name
Dim collNames As Collection
Dim lLen As Long

Set collNames = New Collection
lLen = Len(strStartsWith)

If Len(strStartsWith) 0 Then
For Each oName In ThisWorkbook.Names
If Left$(oName.Name, lLen) = strStartsWith Then
collNames.Add oName.Name
End If
Next
Else
For Each oName In ThisWorkbook.Names
collNames.Add oName.Name
Next
End If

Set GetWBNames = collNames

End Function


Sub test()

Dim i As Long
Dim coll As Collection

Set coll = GetWBNames("A_")

For i = 1 To coll.Count
MsgBox coll(i)
Next

End Sub


RBS


"Randy Harmelink" wrote in message
ups.com...
I have a subset of the following named ranges in several different
worksheets:

A_1, A_2, ..., A_20, B_1, B_2, ..., B_20, C_1, C_2, ..., C_20

Is there a way I can get a collection of those that are available for a
particular sheet using wildcards? For example, something like:

for each oRange in WorkSheets("XXX").Range("A_*")
....processing...
next oRange

I thought about iterating through the workbook names, but the same
names appear on different worksheets within the workbook. There didn't
seem to be a consistency in the "Name" attribute -- the first time it
is defined, it has no sheet name component, but all of the others do.
I suppose I can pick up the sheet name from one of the other attributes
instead (e.g. "RefersTo").

I was hoping there was an existing method to just pick them up from a
given worksheet.

Ah, well -- thanks for the code!

On Nov 6, 12:51 pm, "RB Smissaert"
wrote:
Something like this will do it: