creation of name range without selection of cells

[Guest]

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.

 

[42N83W]

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.

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

 

[Guest]

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