Go To Home

[TONYC]

I would like to be able to go to the top of a worksheet after running
macro which has selecting criteria via an autofilter.

I can obvioulsy mannualy enter Ctrl + Home, but I want to include thi
in a macro. Recording this funcion shows a specific cell reference.

Freeze pane is also active so selecting a cell above the autofilte
range does not show data from the top. (I attempted to add Cell 'A4'
as shown below). A command for Ctrl + Home would be much bettter. I
this possible?

This is my macro...


Sub EnterDept()

Range("C3").Value = InputBox("Please Enter Business Unit", "Al
Risks")
Selection.AutoFilter Field:=1, Criteria1:=Range("C3").Value
If Range("C3") = "" Then ActiveSheet.ShowAllData
Range("A4").Select
End Su

 

[Chip Pearson]

Tony,

Use Application.Goto. E.g.,

Application.Goto Range("A1"), True


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Tom Ogilvy]

Sub EnterDept()
Dim lRow as Long
Dim lCol as Long
Range("C3").Value = InputBox("Please Enter Business Unit", _
"All Risks")
Selection.AutoFilter Field:=1, Criteria1:=Range("C3").Value
If Range("C3") = "" Then ActiveSheet.ShowAllData
lRow = ActiveWindow.SplitRow +1
lCol = ActiveWindow.splitColumn +1
Application.Goto Cells(lRow,lCol)
End Sub

If you just want to go to the top left of your Autofilter

Sub EnterDept()
Range("C3").Value = InputBox("Please Enter Business Unit", _
"All Risks")
Selection.AutoFilter Field:=1, Criteria1:=Range("C3").Value
If Range("C3") = "" Then ActiveSheet.ShowAllData
Application.Goto Activesheet.Autofilter.Range(1)
End Sub

 

[TONYC]

I have tried this but it still dosent take me to the first record belo
the freeze pane line.

Ton

 

[Tom Ogilvy]

Why not try the code I offered. Worked for me.

 

[Dana DeLouis]

Would any ideas here help?

Sub Demo()
If ActiveWindow.FreezePanes Then
With ActiveWindow.ActivePane
Application.Goto Cells(.ScrollRow, .ScrollColumn)
End With
End If
End Sub

HTH
Dana DeLouis

 

[Tom Ogilvy]

That goes to the upper left cell in the current visible area. As long as A4
was that cell, it would work, but it appeared to me he wanted the cell
immediately below the frozen area when there are no hidden rows.

You have to read the thread to get that context.

Just my opinion of course.

 

[TONYC]

Thank you all for your response. After adding the following line to th
macro, it did appear to work to my satisfaction.

Application.Goto Range("A1"), True

Ton

 

[Tom Ogilvy]

Glad you are happy, but to be precise, that isn't the equipvalent to
Ctrl+Home in the situation you describe and in response to that suggestion,
you said: