Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
creation of name range without selection of cells
I need to identify a range of cells in a column when the first cell is a
named range and the last cell is unknown. Myvar = sheet1.range(€śnamed cell and then move down the column to last entry (.End(xlDown)).select. I want to do this without selecting the cells. Is this possible? I generally use a named range and when I need to add an entry within the named range I just insert a new row and the new row is included in my named range and my code can use all the data. In this case I cant insert a new row but the data has room to grow. -- Jim at Eagle |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
creation of name range without selection of cells
"Jim at Eagle" wrote in message ... I need to identify a range of cells in a column when the first cell is a named range and the last cell is unknown. Myvar = sheet1.range("named cell and then move down the column to last entry (.End(xlDown)).select. I want to do this without selecting the cells. Is this possible? I generally use a named range and when I need to add an entry within the named range I just insert a new row and the new row is included in my named range and my code can use all the data. In this case I can't insert a new row but the data has room to grow. -- Jim at Eagle This is a good example where a function comes in handy. I use this one quite a bit. Sub Test() Dim c as Range Dim MyRange as Range Set c = Range("name") Set MyRange = Range(c, rngLBOUND(c)) End Sub Function rngLBOUND(start As Range) As Range 'Recieves a Range object. 'Returns a single cell range object that is the location of the bottom-most value 'in the column occupied by the passed value. Set rngLBOUND = start.Parent.Cells(Rows.Count, start.Column) _ .End(xlUp) End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
creation of name range without selection of cells
I used the following
row1 = Sheet3.Range("B5").End(xlDown).Row ActiveWorkbook.Names.Add Name:="task1summary", RefersToR1C1:= _ "=Summary!R5C2:R" & row1 & "C2" ListBox2.RowSource = "task1Summary" I wanted all within program -- Jim at Eagle |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
duplicate listbox selection to range of cells? | Excel Programming | |||
random selection from a range of cells | Excel Worksheet Functions | |||
Excel VBA - Range(Selection, Selection.End(xlDown)).Name issue. | Excel Programming | |||
Creation of a range with no specified address | Excel Programming | |||
Excel - Copy range of cells based on Combobox Selection | Excel Programming |