Martin,

maybe this

Public lastaddress As String
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
lastaddress = ActiveCell.Address
If ActiveCell.Address = "$E$3" Then Call Hide2002
End Sub


Sub Hide2002()
Range("12:12,29:29,46:46").EntireRow.Hidden = True
Range(lastaddress).Select
Stop
End Sub

Mike

"Martin" wrote:

Thanks, good idea but there are many other hyperlinks on the sheet and i
wanted to try and keep the same look and feel for the user if possible


"OssieMac" wrote:

Hi Martin,

Instead of going to another cell to slect a hyperlink to start the macro,
what about changing to Double Click event in the cell to start macro. That
way you can save the address of the cell.

--
Regards,

OssieMac


"Martin" wrote:

Hi,

I have looked up several postings on last cell and screen positions but cant
seem to get anything to work. Here is a description of my spreadsheet:

I have lots of rows of data wuth Jan to Dec headings and the years 2001 to
2020 as rows. I have this on several series of data and have recorded macros
to hide each year depending on what the users clicks on. I have used
hyperlinks to run the macros:


Hyperlink code is:
If ActiveCell.Address = "$E$3" Then Call Hide2002

Here is the sub:
Sub Hide2002()
Range("12:12,29:29,46:46").Select
Selection.EntireRow.Hidden = True
Range([Go to last position the user was at]).Select
End Sub


My problem is that I want Excel to return to the last screen position or
cell that they clicked in. I can get this to work but unfortunately as the
hyperlink is the last cell they clicked in it returns to that cell. The
hyperlinks are at the top of the sheet, the user may have been in row 502 for
instance.

Any help would be greatly appreciated, I have been trying for hours now!

Martin