Locate cell in Named Range
 

Anyone know how to programatically find the bottom left cell of a named
range? I need to find that cell, and insert an entire row beneath it.
Thanks!

Locate cell in Named Range
 

Hello Steph
This will return address of the bottom left cell:
MyName = ThisWorkbook.Names("YourName").RefersToRange.Value
MsgBox Cells(UBound(MyName, 1), LBound(MyName, 2)).Address

HTH
Cordially
Pascal

"Steph" a écrit dans le message de
...
Anyone know how to programatically find the bottom left cell of a named
range? I need to find that cell, and insert an entire row beneath it.
Thanks!

Locate cell in Named Range
 

try
Sub lrr()
MsgBox Range("rngN").Rows.Count
End Sub
--
Don Guillett
SalesAid Software

"Steph" wrote in message
...
Anyone know how to programatically find the bottom left cell of a named
range? I need to find that cell, and insert an entire row beneath it.
Thanks!

Locate cell in Named Range
 

To insert row to the next line:
Range(Cells(UBound(MyName, 1), LBound(MyName, 2)).Address).Offset(1,
0).EntireRow.Insert

HTH
Cordially
Pascal

"papou" <cestpasbonprobin@çanonpluscg44_fr a écrit dans le message de
...
Hello Steph
This will return address of the bottom left cell:
MyName = ThisWorkbook.Names("YourName").RefersToRange.Value
MsgBox Cells(UBound(MyName, 1), LBound(MyName, 2)).Address

HTH
Cordially
Pascal

"Steph" a écrit dans le message de
...
Anyone know how to programatically find the bottom left cell of a named
range? I need to find that cell, and insert an entire row beneath it.
Thanks!

Locate cell in Named Range
 

myname().Value would raise an error. An array doesn't have an address
property.

--
Regards,
Tom Ogilvy

"papou" <cestpasbonprobin@çanonpluscg44_fr wrote in message
...
Hello Steph
This will return address of the bottom left cell:
MyName = ThisWorkbook.Names("YourName").RefersToRange.Value
MsgBox Cells(UBound(MyName, 1), LBound(MyName, 2)).Address

HTH
Cordially
Pascal

"Steph" a écrit dans le message de
...
Anyone know how to programatically find the bottom left cell of a named
range? I need to find that cell, and insert an entire row beneath it.
Thanks!

Locate cell in Named Range
 

sorry - my mistake - didn't look at the code closely enough.

I should have said this will only work if the range starts in Cell A1.

for instance;
Sub AAABBBDDD()
Range("B9:H30").Name = "YourName"
MyName = ThisWorkbook.Names("YourName").RefersToRange.Value
MsgBox Cells(UBound(MyName, 1), LBound(MyName, 2)).Address
End Sub

Returns A22.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
myname().Value would raise an error. An array doesn't have an address
property.

--
Regards,
Tom Ogilvy

"papou" <cestpasbonprobin@çanonpluscg44_fr wrote in message
...
Hello Steph
This will return address of the bottom left cell:
MyName = ThisWorkbook.Names("YourName").RefersToRange.Value
MsgBox Cells(UBound(MyName, 1), LBound(MyName, 2)).Address

HTH
Cordially
Pascal

"Steph" a écrit dans le message de
...
Anyone know how to programatically find the bottom left cell of a
named
range? I need to find that cell, and insert an entire row beneath it.
Thanks!

Locate cell in Named Range
 

sorry, sent this to you email:

If it is contiguous (a single area range)

set rng = Range("ABCD")
rng.rows(rng.rows.count).offset(1,0).EntireRow.Ins ert

--
Regards,
Tom Ogilvy



"Steph" wrote in message
...
Anyone know how to programatically find the bottom left cell of a named
range? I need to find that cell, and insert an entire row beneath it.
Thanks!

Locate cell in Named Range
 

Hello Tom
The sample code I provided does not use the syntax you mention.
I tested succesfully on my Excel 2003.

Cordially
Pascal

"Tom Ogilvy" a écrit dans le message de
...
myname().Value would raise an error. An array doesn't have an address
property.

--
Regards,
Tom Ogilvy

"papou" <cestpasbonprobin@çanonpluscg44_fr wrote in message
...
Hello Steph
This will return address of the bottom left cell:
MyName = ThisWorkbook.Names("YourName").RefersToRange.Value
MsgBox Cells(UBound(MyName, 1), LBound(MyName, 2)).Address

HTH
Cordially
Pascal

"Steph" a écrit dans le message de
...
Anyone know how to programatically find the bottom left cell of a
named
range? I need to find that cell, and insert an entire row beneath it.
Thanks!

Locate cell in Named Range
 

Yes, it was my mistake. It does work if the range starts in A1. Otherwise,
wrong answer.
See my previous post stating this.

--
Regards,
Tom Ogilvy

"papou" <cestpasbonprobin@çanonpluscg44_fr wrote in message
...
Hello Tom
The sample code I provided does not use the syntax you mention.
I tested succesfully on my Excel 2003.

Cordially
Pascal

"Tom Ogilvy" a écrit dans le message de
...
myname().Value would raise an error. An array doesn't have an address
property.

--
Regards,
Tom Ogilvy

"papou" <cestpasbonprobin@çanonpluscg44_fr wrote in message
...
Hello Steph
This will return address of the bottom left cell:
MyName = ThisWorkbook.Names("YourName").RefersToRange.Value
MsgBox Cells(UBound(MyName, 1), LBound(MyName, 2)).Address

HTH
Cordially
Pascal

"Steph" a écrit dans le message de
...
Anyone know how to programatically find the bottom left cell of a
named
range? I need to find that cell, and insert an entire row beneath
it.
Thanks!

Locate cell in Named Range
 

Tom
You're right, unfortunately this does not seem to work for names not
starting in A1

Cordially
Pascal

"Tom Ogilvy" a écrit dans le message de
...
sorry - my mistake - didn't look at the code closely enough.

I should have said this will only work if the range starts in Cell A1.

for instance;
Sub AAABBBDDD()
Range("B9:H30").Name = "YourName"
MyName = ThisWorkbook.Names("YourName").RefersToRange.Value
MsgBox Cells(UBound(MyName, 1), LBound(MyName, 2)).Address
End Sub

Returns A22.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
myname().Value would raise an error. An array doesn't have an address
property.

--
Regards,
Tom Ogilvy

"papou" <cestpasbonprobin@çanonpluscg44_fr wrote in message
...
Hello Steph
This will return address of the bottom left cell:
MyName = ThisWorkbook.Names("YourName").RefersToRange.Value
MsgBox Cells(UBound(MyName, 1), LBound(MyName, 2)).Address

HTH
Cordially
Pascal

"Steph" a écrit dans le message de
...
Anyone know how to programatically find the bottom left cell of a
named
range? I need to find that cell, and insert an entire row beneath
it.
Thanks!

Locate cell in Named Range
 

Thanks everyone!

"Tom Ogilvy" wrote in message
...
sorry, sent this to you email:

If it is contiguous (a single area range)

set rng = Range("ABCD")
rng.rows(rng.rows.count).offset(1,0).EntireRow.Ins ert

--
Regards,
Tom Ogilvy



"Steph" wrote in message
...
Anyone know how to programatically find the bottom left cell of a named
range? I need to find that cell, and insert an entire row beneath it.
Thanks!