CODEWITHSUNDEEP
excelvba
bob
bob

Reputation: 1

VBA Excel select named range starting with character

I've got a little problem with named ranges.

I have multiple named ranges on different sheets. i would like to hide of show all those ranges with vba. example of named range: r1_name1 . the 2nd on another sheet is r1_name2. So all the ranges starts with the same code in front: r1_ .

How can i loop through all ranges that starts with r1_ and hide/or show them?

Upvotes: 0

Views: 544

Answers (2)

srsharpe33
srsharpe33

Reputation: 1

Here's a little code to hide names in a workbook that include a particular string, in this case "_Filter". While I was doing a particular search one time, I unhid all named ranges in my workbook. After deleting the names I didn't want anymore, I then wanted to re-hide the names related to filters.

Sub HideNames_Filter()

Dim nName As Name

    For Each nName In ActiveWorkbook.Names
        If InStr(nName.Name, "_Filter") Then
            nName.Visible = False
        End If
    Next

End Sub

Upvotes: 0

tigeravatar
tigeravatar

Reputation: 26640

To loop through the named ranges:

Sub tgr()

    Dim NamedRange As Name

    For Each NamedRange In ActiveWorkbook.Names
        If LCase(Left(NamedRange.Name, 3)) = "r1_" Then
            MsgBox NamedRange.Name & Chr(10) & _
                   Range(NamedRange.RefersTo).Address(External:=True)
        End If
    Next NamedRange

End Sub

To hide/unhide them them:

Range(NamedRange.RefersTo).EntireRow.Hidden = True 'or False

Upvotes: 2

Related Questions