Locate cell in Named Range

[Steph]

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!

 

[papou]

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

 

[Don Guillett]

try
Sub lrr()
MsgBox Range("rngN").Rows.Count
End Sub

 

[papou]

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

HTH
Cordially
Pascal

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

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.Insert

 

[papou]

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]

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

 

[papou]

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

Cordially
Pascal

 

[Steph]

Thanks everyone!

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.Insert