easy one - select a cell

[Guest]

Hi

I want to be able to select a range eg
Application.Goto Reference:="Asset"

Once I have done this I want to be able to deduce what the first cell in the
range is eg "F1". I want to do this so I can reset the range when people add
to it

Also is there an easier way to write code to resize the range ie below is
recorded but I just want to be able to say =range("F1","F11") etc
ActiveWorkbook.Names.Add Name:="Asset", RefersToR1C1:= _
"=Tables!R2C6:R11C6"


Thanks

 

[Dave Peterson]

activesheet.range("f1:f11").name = "asset"

But you may want to look at using a dynamic range name that can grow and
contract...

Debra Dalgleish explains it:
http://contextures.com/xlNames01.html#Dynamic

 

[Mike Fogleman]

Here is one way to resize a named range that has items added:

Sub test()
Dim NewRng As Range
Set NewRng = Range("Asset").CurrentRegion
ActiveWorkbook.Names.Add Name:="Asset", RefersTo:=NewRng
End Sub

The CurrentRegion is all non empty cells that are adjacent (contiguous) with
named range "Asset"
Mike F

 

[Gary Keramidas]

just some extra info, select used for illustration:

if you have a range selected, this will select the upper left cell.
ActiveCell.CurrentRegion.Cells(1, 1).Select

if you want to select the upper left cell in a range using the name:
ActiveSheet.Range("Range_Name").Cells(1, 1).Select