Given a cell, find the named range it belongs to

  • Thread starter Thread starter Dag Johansen
  • Start date Start date
D

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?
 
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
 
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

Dag Johansen said:
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?

.
Click to expand...
Click to expand...
 
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?

.
Click to expand...
.
Click to expand...
 
Back
Top