Thank you to all that replied and helped.

OssieMac - your code worked the best and returns to the last cell perfectly.

Thanks again,

Martin

"OssieMac" wrote:

Hi Mike,

Perhaps I am completely missing something here?
You are saving lastaddress = ActiveCell.Address
and then testing If ActiveCell.Address = "$E$3"
Surely lastaddress is going to be "$E$3"

To Martin,

You said that the Hyperlinks are all at the top of the sheet. Does that mean
that they are out of the range that you would be likely to want to return to?
If so, you might like to try saving the last selected cell (if outside the
Hyperlink area) like the following. Doesn't matter that it always gets saved
whether or not you actually click a hyperlink but when you do click a
hyperlink you get the last selected cell before selecting the Hyperlink.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim isect As Object

'Testing if in first 3 rows of worksheet
Set isect = Application.Intersect(Target, Range("1:3"))

If isect Is Nothing Then
'Selected cell not in first 3 rows (Hyperlink Area)
'Therefore save the ActiveCell address
'at any spare cell on the worksheet.
Range("K1") = ActiveCell.Address
End If

End Sub

Sub Hide2002()

Dim strRngK1 As String

strRngK1 = Range("K1")

Range("12:12,29:29,46:46").Select
Selection.EntireRow.Hidden = True

Range(strRngK1).Select

End Sub

--
Regards,

OssieMac


"Mike H" wrote:

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