set range based on activecell and what range it is, but name must contain certain word

[wbstadeli]

Hi all,

I have a worksheet with a lot of named ranges in it. My main named ranges are named "Item_Range_(Variable)" with the variable being a number so that the named ranges are unique. What i would like to do is in my macro set range to equal whichever range that the activecell is in and contains "Item_Range" in the name. How would this be coded? Thanks!

 

[onlyadrafter]

Hello,

Does this work as expected?

Sub WHOLE_RANGES()
    Range("Z1").ListNames
    For MY_ROWS = 1 To Range("Z" & Rows.Count).End(xlUp).Row
        If Not Intersect(ActiveCell, Range(Range("Z" & MY_ROWS).Value)) Is Nothing Then
            Range(Range("Z" & MY_ROWS).Value).Select ' YOU CAN DELETE THIS LINE
            MY_RANGE = Range("Z" & MY_ROWS).Value
            GoTo MY_END
        End If
    Next MY_ROWS
MY_END:
Columns("Z:AA").ClearContents
End Sub

I have assumed columns Z and AA are available. Is not, change all Z to a column that is available and change Z:AA to your available column and the one to the right of it.

 

[wbstadeli]

Interesting approach! Thank you for your response, i was able to get what i need with this:

     Private Sub Worksheet_Change(ByVal target As Range)
    '.....
    Dim namItem As Name
    For Each namItem In ThisWorkbook.Names
        'match a certain partial range name, this example: Quoted_Price_Range_'
        If (Left(namItem.Name, 19) = "Quoted_Price_Range_")  Then
            If (Not (Intersect(target, namItem.RefersToRange) Is Nothing)) Then
                'do code'
            End If
        End If
    Next
    '......'