Selecting a cell
 

Hi,

In my spreadsheet I have a range of cells (in one column)
which I have given a name, MYVARS.

Thus MYVARS is physically the Sheet2!A1:A17 range.

Now, from within a macro, I want to provide this name
(MYVARS) and the macro must find out its real range and go
to the last cell in that range;

Thus, if I provide MYVARS as input to the macro, it should
position my cursor to the cell Sheet2!A17 because that is
the last cell in the range MYVARS.

Can somebody help?

Selecting a cell
 

You could try the UsedRange property and then parse the last part of the
result which would be the last cell in the range. This only works if
the sheet is clean and there are no blanks at the end of the list.

Someone once sent me this code which returns the range of used rows and
columns. You can then use this to figure out what the last row would be
by using UsedCnt as part of your new Range:


DetermineUsedRange usedRng
UsedCnt = usedRng.Rows.count

Sub DetermineUsedRange(ByRef theRng As Range)
Dim FirstRow As Integer, FirstCol As Integer, _
LastRow As Integer, LastCol As Integer
On Error GoTo handleError
FirstRow = Cells.Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByRows).Row
FirstCol = Cells.Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByColumns).Column
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
LastCol = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
Set theRng = Range(Cells(FirstRow, FirstCol), _
Cells(LastRow, LastCol))
handleError:
End Sub



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Selecting a cell
 

set rng = worksheets("Sheet2").Range("Myvars")
Application.Goto rng(rng.count)


Should work.

--
Regards,
Tom Ogilvy

"Bob" wrote in message
...
Hi,

In my spreadsheet I have a range of cells (in one column)
which I have given a name, MYVARS.

Thus MYVARS is physically the Sheet2!A1:A17 range.

Now, from within a macro, I want to provide this name
(MYVARS) and the macro must find out its real range and go
to the last cell in that range;

Thus, if I provide MYVARS as input to the macro, it should
position my cursor to the cell Sheet2!A17 because that is
the last cell in the range MYVARS.

Can somebody help?

Selecting a cell
 

try this

Sub gotolastcell()
x = [myvars].SpecialCells(xlLastCell).Address
Range(x).Select
End Sub

or a one liner
Sub ddd()
Range([myvars].SpecialCells(xlLastCell).Address).Select
End Sub


"Bob" wrote in message
...
Hi,

In my spreadsheet I have a range of cells (in one column)
which I have given a name, MYVARS.

Thus MYVARS is physically the Sheet2!A1:A17 range.

Now, from within a macro, I want to provide this name
(MYVARS) and the macro must find out its real range and go
to the last cell in that range;

Thus, if I provide MYVARS as input to the macro, it should
position my cursor to the cell Sheet2!A17 because that is
the last cell in the range MYVARS.

Can somebody help?

Selecting a cell
 

OP said In my spreadsheet I have a range of cells (in one column)
I guess I should not have assumed that this was the ONLY column
so
Sub ddddd()' this should work
Range([myvars].Range("a65536").End(xlUp).Address).Select
End Sub

"Tom Ogilvy" wrote in message
...
Just for information -
That goes to the last cell in the usedrange of the sheet, not the last
cell
in the range myvars.


Regards,
Tom Ogilvy


"Don Guillett" wrote in message
...
try this

Sub gotolastcell()
x = [myvars].SpecialCells(xlLastCell).Address
Range(x).Select
End Sub

or a one liner
Sub ddd()
Range([myvars].SpecialCells(xlLastCell).Address).Select
End Sub


"Bob" wrote in message
...
Hi,

In my spreadsheet I have a range of cells (in one column)
which I have given a name, MYVARS.

Thus MYVARS is physically the Sheet2!A1:A17 range.

Now, from within a macro, I want to provide this name
(MYVARS) and the macro must find out its real range and go
to the last cell in that range;

Thus, if I provide MYVARS as input to the macro, it should
position my cursor to the cell Sheet2!A17 because that is
the last cell in the range MYVARS.

Can somebody help?