It’s often handy to be able to select the name range that contains the current cell (for example, to change the range formatting). If you know the name of the range, you need only select it from the Name box.

However, in a large model or a workbook that you’re not familiar with, it may not be obvious which name to choose. Listing 6.6 shows a VBA function and procedure that handles this chore for you.

Function GetRangeName(r As Range) As String
Dim n As Name
Dim rtr As Range
Dim ir As Range
‘
‘ Run through all the range names in the active workbook
‘
For Each n In ActiveWorkbook.Names
‘
‘ Get the name’s range
‘
Set rtr = n.RefersToRange
‘
‘ See if the named range and the active cell’s range intersect
‘
Set ir = Application.Intersect(r, rtr)
If Not ir Is Nothing Then
‘
‘ If they intersect, then the active cell is part of a
‘ named range, so get the name and exit the function
GetRangeName = n.Name
Exit Function
End If
Next ‘n
‘
‘ If we get this far, the active cell is not part of a named range,
‘ so return the null string
‘
GetRangeName = “”
End Function
Sub SelectCurrentNamedRange()
Dim r As Range
Dim strName As String
‘
‘ Store the active cell
‘
Set r = ActiveCell
‘
‘ Get the name of the range that contains the cell, if any
‘
strName = GetRangeName(r)
If strName <> “” Then
‘
‘ If the cell is part of a named range, select the range
‘
Range(strName).Select
End If
End Sub

The heart of Listing 6.6 is the GetRangeName function, which takes a range as an argument. The purpose of this function is to see if the passed range—r—is part of a named range and if so, to return the name of that range. The function’s main loop runs through each item in the active workbook’s Names collection. For each name, the RefersToRange property returns the associated range, which the function stores in the rtr variable.

The function then uses the Intersect method to see if the ranges r and rtr intersect. If they do, it means that r is part of the named range (because, in this case, r is just a single cell), so GetRangeName returns the range name. If no intersection is found for any name, the function returns the null string (“”) instead.

The SelectCurrentNamedRange procedure makes use of the GetRangeName function. The procedure stores the active cell in the r variable and then passes that variable to the GetRangeName function. If the return value is not the null string, the procedure selects the returned range name.

• Improve Your Blog Functionality You can improve your blog functionality in order to improve your traffic, readership and the number of people that comment...
• Tips for Teaching Tennis The bottom line when it comes to coaching or teaching tennis is that if you have a strong beginner's tennis...
• 5 Tips and Tricks for your iPhone [/caption]I’m not really a read the manual kind of guy.  It has nothing to do with being a typical man...
• 4 Weight Loss Tips and Tricks The following weight loss tips and tricks are designed to help you lose weight more effectively and more efficiently than...
• Weight Loss Tips and Tricks When it comes to losing weight, you probably know the drill. Burn more calories than you consume, eat healthy, and...