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 |
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 |
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 |
All times are GMT +1. The time now is 07:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com