Retriving an Excel range name for the active cell

[Stevie D]

Hi,

My Excel macro loops through a each cell in a large 2 dimensional named
range (cells A1:I9). This range is sub divided in to smaller two dimensional
named ranges called Grid1 through to Grid9. Each of these smaller named
ranges encompasses 9 cells e.g. Grid1 = A1:C3, Grid2 = D1:F3 etc.

Is it possible to retrieve the name of the smaller named range that the
active cell is in e.g if the active cell is B1 I want the macro to return
Grid1?

Thanks in advance,

Steve

 

[IMS.Rushikesh]

Hi Stevie,

I don't know about Macro will do it or not.....

But had implemented same job in C#...Please check the below code....

for(int i = 1; i <= selection.Areas.Count; i++)
{
for(int rowNo = 1 ; rowNo <= selection.Areas.Rows.Count; rowNo++)
{
for(int colNo = 1 ; colNo <= selection.Areas.Columns.Count;
colNo++)
{

Consol.Print(((Excel.Range)selection.Areas.Cells[rowNo,colNo]).Name.Name);
}
}
}

//See here if user will select multiple cell using 'Ctrl' key then this
different selection is stored as different Area...
//now for each Area user has to take rows and columns and convert a
cell to Excel.Range....
//Now Excel.Range have property Name which will give you a Name for
that cell.
//hope this information is sufficient for you.


Thanks & Regards,
Rushikesh

 

[Stevie D]

Hi Rushikesh

Unfortunately I don't have any experience with C# and I'm fairly new to VBA
so I'm sorry but I can't follow this code.

Thanks anyway.

Steve.

Hi Stevie,

I don't know about Macro will do it or not.....

But had implemented same job in C#...Please check the below code....

for(int i = 1; i <= selection.Areas.Count; i++)
{
for(int rowNo = 1 ; rowNo <= selection.Areas.Rows.Count; rowNo++)
{
for(int colNo = 1 ; colNo <= selection.Areas.Columns.Count;
colNo++)
{

Consol.Print(((Excel.Range)selection.Areas.Cells[rowNo,colNo]).Name.Name)
;
}
}
}

//See here if user will select multiple cell using 'Ctrl' key then this
different selection is stored as different Area...
//now for each Area user has to take rows and columns and convert a
cell to Excel.Range....
//Now Excel.Range have property Name which will give you a Name for
that cell.
//hope this information is sufficient for you.


Thanks & Regards,
Rushikesh

Hi,

My Excel macro loops through a each cell in a large 2 dimensional named
range (cells A1:I9). This range is sub divided in to smaller two dimensional
named ranges called Grid1 through to Grid9. Each of these smaller named
ranges encompasses 9 cells e.g. Grid1 = A1:C3, Grid2 = D1:F3 etc.

Is it possible to retrieve the name of the smaller named range that the
active cell is in e.g if the active cell is B1 I want the macro to return
Grid1?

Thanks in advance,

Steve

 

[RMA]

Hi Stevie,

Since range names are stored alphabetically as a VBA collection at the
Workbook level, it is a bit tricky to get the exact named range you want.( It
is the same when you have to select the exact range for the Range name to
pop-up in the box). The following macro will help you retrieve the sub-range
of the currently selected cell. This assumes that all your sub-ranges begin
with Grid (Grid1, Grid2 etc.) If you change that, you will have to change the
Instr function accordingly.

Sub Find_RangeNames()
Dim n As Name

For Each n In ActiveWorkbook.Names
If InStr(1, n.Name, "Grid", vbTextCompare) > 0 Then 'Don't check for
parent/other
Set y = Intersect(ActiveCell, Range(n.RefersTo))
' Display a message box if the ActiveCell is in the named range.
If Not y Is Nothing Then MsgBox "Cell is in : " & n.Name
End If
Next n
End Sub