Selecting a cell

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

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?
 
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
 
set rng = worksheets("Sheet2").Range("Myvars")
Application.Goto rng(rng.count)


Should work.
 
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
 
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 said:
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 said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top