hyperlinking to the LAST used cell on a sheet

[jonnygee]

I have a large workbook with many pages. The first page is an 'Index' page that has hyperlinks to all the other pages by name.
When the hyperlink is made by Excel, it automatically makes the link to the 'A1' cell on each page. Actually I want it to link to the LAST used cell on each page.
Where it says "Type the cell reference" I tried entering 'xlCellTypeLastCell" or "=xlCellTypeLastCell" but it refused these as "Reference is not valid". It puts a default value of 'A1' here, and I could obviously change it to any specific cell, but I want it to dynamically go to the LAST cell.
So, how do I get the hyperlink to go to the last used cell of the page it points to?
(The last used cell is a dynamic position, because it depends on the latest entries -- in this case they are the lowest on the page).

Thanks in advance,

Jonathan

 

[Juan Pablo González]

This is not doable without VBA. Is that a possiblity ?

 

[jonnygee]

Sure. Would I have to add code to each and every hyperlink, though. That might not be worth the trouble. But what did you have in mind?

Using Excel2000 under Win98SE

Thanks,

Jonathan

 

[Juan Pablo González]

No, I created this UDF:<font face=Courier New><SPAN style="color:#00007F">Option</SPAN><SPAN style="color:#00007F">Explicit</SPAN><SPAN style="color:#00007F">Function</SPAN> LASTCELL(<SPAN style="color:#00007F">Optional</SPAN> Rng<SPAN style="color:#00007F">As</SPAN> Range,<SPAN style="color:#00007F">Optional</SPAN> Volatile<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Boolean</SPAN>)<SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> WS<SPAN style="color:#00007F">As</SPAN> Worksheet, lRow<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, lCol<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> CRow<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, CCol<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>
    Application.Volatile Volatile
    <SPAN style="color:#00007F">If</SPAN> Rng<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> Rng = Application.Caller.Parent.Cells
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#007F00">'Set WS = Rng.Parent</SPAN>
    <SPAN style="color:#00007F">With</SPAN> Intersect(Rng, Rng.Parent.UsedRange)
        lRow = .Rows.Count
        lCol = .Columns.Count
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">Do</SPAN>
        <SPAN style="color:#00007F">With</SPAN> Rng.Cells(lRow, Rng.Columns.Count)
            <SPAN style="color:#00007F">If</SPAN> Len(.Formula) > 0<SPAN style="color:#00007F">Then</SPAN>
                CRow = .Column
            <SPAN style="color:#00007F">Else</SPAN>
                CRow = .End(xlToLeft).Column
            <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">If</SPAN> CRow< Rng.Column<SPAN style="color:#00007F">Then</SPAN> CRow = 1
        <SPAN style="color:#00007F">If</SPAN> CRow = 1 And Len(Rng.Cells(lRow, CRow).Formula) > 0<SPAN style="color:#00007F">Then</SPAN>
            CRow = 0
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
        
        <SPAN style="color:#00007F">With</SPAN> Rng.Cells(Rng.Rows.Count, lCol)
            <SPAN style="color:#00007F">If</SPAN> Len(.Formula) > 0<SPAN style="color:#00007F">Then</SPAN>
                CCol = .Row
            <SPAN style="color:#00007F">Else</SPAN>
                CCol = .End(xlUp).Row
            <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">If</SPAN> CCol< Rng.Row<SPAN style="color:#00007F">Then</SPAN> CCol = 1
        <SPAN style="color:#00007F">If</SPAN> CCol = 1 And Len(Rng.Cells(CCol, lCol).Formula) > 0<SPAN style="color:#00007F">Then</SPAN>
            CCol = 0
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">If</SPAN> CRow = 1<SPAN style="color:#00007F">Then</SPAN>
            lRow = lRow - 1
        <SPAN style="color:#00007F">ElseIf</SPAN> CCol = 1<SPAN style="color:#00007F">Then</SPAN>
            lCol = lCol - 1
        <SPAN style="color:#00007F">Else</SPAN>
            <SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Do</SPAN>
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Loop</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> LASTCELL = Rng.Cells(lRow, lCol)<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN></FONT>

That you could use like this:

LastCell.xls
	A	B	C	D	E	F	G	H	I
1		FinitRange	AllCells	Allcellsincallingsheet(Omittedrange)
2	Row	16	20	20
3	Column	8	9	9
4	Address	$H$16	$I$20	$I$20
5
6	Hyperlink	Goto$H$16	Goto$I$20	Goto$I$20
7
8
9				1				1
10						1
11
12
13				8			1
14
15
16								a
17
18
19
20									a
Hoja1

 

[jonnygee]

Juan, thank you for the function, but I am not sure how I would use it. I didn't understand the 'page' at the bottom of your post. My INDEX page is one worksheet out of, say, a hundred or more sheets in the workbook. When I click on a hyperlink on the INDEX page, I want the cursor to go to the last used cell on the target page. I wasn't clear on where I call LASTCELL from. Shouldn't it be called automatically when I click on a hyperlink?

Thanks,

Jonathan

 

[Juan Pablo González]

What LASTCELL does is return the last cell of a given range... no matter where, and it returns that as a reference, that's why we can use ROW() or COLUMN() to find the "actual" address of it.

I created this Index sheet, and I had to make a couple of small modifications to the code for it to work:

LastCell.xls
	A	B	C	D
1		Lastcell
2	Hoja1	[LastCell.xls]Hoja1!I20
3	Hoja2	[LastCell.xls]Hoja2!B5
4	Hoja3	[LastCell.xls]Hoja3!A1
INDEX

The formula in B2 is:

=HYPERLINK("[LastCell.xls]"&ADDRESS(ROW(LASTCELL(INDIRECT("'"&A2&"'!$1:$65536"),1)),COLUMN(LASTCELL(INDIRECT("'"&A2&"'!$1:$65536"),1)),4,,A2))

And the code is:<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> LASTCELL(<SPAN style="color:#00007F">Optional</SPAN> Rng<SPAN style="color:#00007F">As</SPAN> Range,<SPAN style="color:#00007F">Optional</SPAN> Volatile<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Boolean</SPAN>)<SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> WS<SPAN style="color:#00007F">As</SPAN> Worksheet, lRow<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, lCol<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> CRow<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, CCol<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>
    Application.Volatile Volatile
    <SPAN style="color:#00007F">If</SPAN> Rng<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> Rng = Application.Caller.Parent.Cells
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">On</SPAN><SPAN style="color:#00007F">Error</SPAN><SPAN style="color:#00007F">GoTo</SPAN> exiting
    <SPAN style="color:#00007F">With</SPAN> Intersect(Rng, Rng.Parent.UsedRange)
        lRow = .Rows.Count
        lCol = .Columns.Count
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> Rng = Intersect(Rng, Rng.Parent.UsedRange)
    <SPAN style="color:#00007F">Do</SPAN>
        <SPAN style="color:#00007F">With</SPAN> Rng.Cells(lRow, Rng.Columns.Count)
            <SPAN style="color:#00007F">If</SPAN> Len(.Formula) > 0<SPAN style="color:#00007F">Then</SPAN>
                CRow = .Column
            <SPAN style="color:#00007F">Else</SPAN>
                CRow = .End(xlToLeft).Column
            <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">If</SPAN> CRow< Rng.Column<SPAN style="color:#00007F">Then</SPAN> CRow = 1
        <SPAN style="color:#00007F">If</SPAN> CRow = 1 And Len(Rng.Cells(lRow, CRow).Formula) > 0<SPAN style="color:#00007F">Then</SPAN>
            CRow = 0
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
        
        <SPAN style="color:#00007F">With</SPAN> Rng.Cells(Rng.Rows.Count, lCol)
            <SPAN style="color:#00007F">If</SPAN> Len(.Formula) > 0<SPAN style="color:#00007F">Then</SPAN>
                CCol = .Row
            <SPAN style="color:#00007F">Else</SPAN>
                CCol = .End(xlUp).Row
            <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">If</SPAN> CCol< Rng.Row<SPAN style="color:#00007F">Then</SPAN> CCol = 1
        <SPAN style="color:#00007F">If</SPAN> CCol = 1 And Len(Rng.Cells(CCol, lCol).Formula) > 0<SPAN style="color:#00007F">Then</SPAN>
            CCol = 0
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">If</SPAN> CRow = 1<SPAN style="color:#00007F">Then</SPAN>
            lRow = lRow - 1
        <SPAN style="color:#00007F">ElseIf</SPAN> CCol = 1<SPAN style="color:#00007F">Then</SPAN>
            lCol = lCol - 1
        <SPAN style="color:#00007F">Else</SPAN>
            <SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Do</SPAN>
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Loop</SPAN>
exiting:
    <SPAN style="color:#00007F">If</SPAN> lRow = 0<SPAN style="color:#00007F">Then</SPAN> lRow = 1
    <SPAN style="color:#00007F">If</SPAN> lCol = 0<SPAN style="color:#00007F">Then</SPAN> lCol = 1
    <SPAN style="color:#00007F">Set</SPAN> LASTCELL = Rng.Cells(lRow, lCol)<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Function</SPAN></FONT>

My sheets are named Hoja1, Hoja2 and Hoja3, but you could extend this to all the sheets that you want.