Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
selecting a cell | Excel Discussion (Misc queries) | |||
Excel 2007 single cell selecting muliple cell | Excel Worksheet Functions | |||
Selecting a cell entry based on cell validation selection | Excel Worksheet Functions | |||
Transfer cell values to another cell by selecting button. | Excel Worksheet Functions | |||
Selecting A cell... | Excel Discussion (Misc queries) |