| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| locate a cell value within a range and return its reference | Excel Discussion (Misc queries) | |||
| Referencing a named range based upon Range name entry in cell | Excel Worksheet Functions | |||
| inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
| named cell range | Excel Worksheet Functions | |||
| Getting a named range from a cell value | Excel Worksheet Functions | |||
![Steph[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
