Ms Office Tune Up

It’s often handy to be able to select the name range that contains the current cell (for exam­ple, 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 1 shows a VBA func­tion and procedure that handles this chore for you.

Listing 1 A VBA Function and Procedure That Determines and Selects the Named Range Containing the Active Cell

The heart of Listing 1 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.

Technorati Tags: named range, contains active cell, excel 2007

Popularity: 2% [?]

If you liked this post, would you please buy me a twelve-ounce lattes for only $2