Problem getting name of range with only one cell

[ramp0144]

Hi all,

I'm facing a pretty peculiar problem in both, excel 2007 and 2010. I have created a vertical range(say, 'TestRng'- $A$1:$A$5). Now I delete last 4 rows, i.e. 2 through 5. 'TestRng' now refers to $A1:$A1(when I see it in name manager). Now if I select this cell and try to fetch its name using vba code:
selection.name.name

I get error. (Runtime error 1004. Application defined or object defined error)

However, if I create a named range with just one cell(say, 'TestRng1' - $B$1), in the name manager it shows as referring to $B$1, and not $B$1:$B$1(as in the former case). Now if I select this cell and try to fetch its name using vba code:
selection.name.name
It works, i.e. I get correct name-'TestRng1'

Can you help me get the correct name in the former case(where last n-1 rows are deleted)?

[Mallycat]

This is not the way to do this. You should convert your range to a "table". Use the menu item "insert\table". Tables are designed to solve this issue (among other things)

[ramp0144]

Thanks Mallycat,
however, I'm working on an existing template and need a way out using ranges. Cannot replace all required ranges with tables.

[kvsrinivasamurthy]

Pl see the attached file with macro.
macro defines name for the range which contains data from A1 to end of column A.
k returns the name of range.

Any clarifications welcome.

[ramp0144]

Thanks kvsrinivasamurthy, really appreciate your help. However, the problem still persists.

In the sheet u sent, if u ran your macro, it generated a range named 'erange1' - $A$1:$A$5. You can try this out on the same sheet:
Delete last 4 rows, i.e. rows 2 to 5, such that range 'erange1' now references only one cell i.e. $A$1. Now if I select this cell and try to get it's name using:
selection.name.name
It gives error that I mentioned in the first post of this thread.

Any help would be appreciated.

[ramp0144]

Thanks a lot Rory.

I was aware of this. But there are 400-500 names in the template and this part itself is inside a loop that runs about as many times. Not sure if there is a more efficient solution to this problem.

[ramp0144]

Thanks Rory,

That is exactly how I have implemented, using exit for. May be I will have to be content with this, for the time being.

Regards,
Rahul