Given a cell, find the named range it belongs to

[Dag Johansen]

Hi,

how can I reliably and efficiently determine if a cell "is
a member of" a named range, i.e. if it is part of a named
range?

It should work looping trough all Names, determine if the
name refers to a range, and if so loop through the cells
of the range and compare with the "pivot" (the given cell)
using the Is operator. If a match is found, return the
named range (i.e. stop looping immediately); if loop
terminates, return Nothing.

But is there a better way? It seems to me if there are
lots of/huge named ranges in a workbook this method might
be a little slow.

Any ideas?

 

[Dag Johansen]

Hi again,

I found what I was looking for. To anyone else in need of
the same or something similar: The Range has a property
called CurrentRegion for this purpose.

Function GetNamedRange(subRange As Range) As Range
On Error Goto NOT_IN_NAMED_RANGE
Set GetNamedRange = subRange.CurrentRegion.Name
Exit Function
NOT_IN_NAMED_RANGE:
Set GetNamedRange = Nothing
End Function

Example: The range [A1:C5] has been named "myRange". Thus
GetNamedRange( Range("A3") ) returns the "myRange" range
[A1:C5]. Quite useful!

Happy coding!

Dag

 

[Tom Ogilvy]

While this may work for your special case, it certianly doesn't do what you
described in your original post. Also, you are doing some implicit
conversions

? Range("A1").CurrentRegion.address
$A$1:$C$5
? Range("A1").CurrentRegion.name
=Sheet1!$A$1:$C$5
? Range("A1").CurrentRegion.name.name
MyRange


--
Regards,
Tom Ogilvy

Hi again,

I found what I was looking for. To anyone else in need of
the same or something similar: The Range has a property
called CurrentRegion for this purpose.

Function GetNamedRange(subRange As Range) As Range
On Error Goto NOT_IN_NAMED_RANGE
Set GetNamedRange = subRange.CurrentRegion.Name
Exit Function
NOT_IN_NAMED_RANGE:
Set GetNamedRange = Nothing
End Function

Example: The range [A1:C5] has been named "myRange". Thus
GetNamedRange( Range("A3") ) returns the "myRange" range
[A1:C5]. Quite useful!

Happy coding!

Dag

-----Original Message-----
Hi,

how can I reliably and efficiently determine if a cell "is
a member of" a named range, i.e. if it is part of a named
range?

It should work looping trough all Names, determine if the
name refers to a range, and if so loop through the cells
of the range and compare with the "pivot" (the given cell)
using the Is operator. If a match is found, return the
named range (i.e. stop looping immediately); if loop
terminates, return Nothing.

But is there a better way? It seems to me if there are
lots of/huge named ranges in a workbook this method might
be a little slow.

Any ideas?

.

 

[Walt Weber]

Hi Dag,

The following I found in the VBA help system seems to
address your need:
"
Example
This example selects the intersection of two named ranges,
rg1 and rg2, on Sheet1. If the ranges don't intersect, the
example displays a message.

Worksheets("Sheet1").Activate
Set isect = Application.Intersect(Range("rg1"), Range
("rg2"))
If isect Is Nothing Then
MsgBox "Ranges do not intersect"
Else
isect.Select
End If
"

Best Regards,
Walt

-----Original Message-----
Hi again,

I found what I was looking for. To anyone else in need of
the same or something similar: The Range has a property
called CurrentRegion for this purpose.

Function GetNamedRange(subRange As Range) As Range
On Error Goto NOT_IN_NAMED_RANGE
Set GetNamedRange = subRange.CurrentRegion.Name
Exit Function
NOT_IN_NAMED_RANGE:
Set GetNamedRange = Nothing
End Function

Example: The range [A1:C5] has been named "myRange". Thus
GetNamedRange( Range("A3") ) returns the "myRange" range
[A1:C5]. Quite useful!

Happy coding!

Dag

-----Original Message-----
Hi,

how can I reliably and efficiently determine if a cell "is
a member of" a named range, i.e. if it is part of a named
range?

It should work looping trough all Names, determine if the
name refers to a range, and if so loop through the cells
of the range and compare with the "pivot" (the given cell)
using the Is operator. If a match is found, return the
named range (i.e. stop looping immediately); if loop
terminates, return Nothing.

But is there a better way? It seems to me if there are
lots of/huge named ranges in a workbook this method might
be a little slow.

Any ideas?

.

.