Go To Home
 

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

--
Message posted from http://www.ExcelForum.com

Go To Home
 

Tony,

Use Application.Goto. E.g.,

Application.Goto Range("A1"), True


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




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

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

Freeze pane is also active so selecting a cell above the
autofilter
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. Is
this possible?

This is my macro...


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
Range("A4").Select
End Sub


---
Message posted from http://www.ExcelForum.com/

Go To Home
 

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

--
Regards,
Tom Ogilvy


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

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

Freeze pane is also active so selecting a cell above the autofilter
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. Is
this possible?

This is my macro...


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
Range("A4").Select
End Sub


---
Message posted from http://www.ExcelForum.com/

Go To Home
 

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

Ton

--
Message posted from http://www.ExcelForum.com

Go To Home
 

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

--
Regards,
Tom Ogilvy

"TONYC " wrote in message
...
I have tried this but it still dosent take me to the first record below
the freeze pane line.

Tony


---
Message posted from http://www.ExcelForum.com/

Go To Home
 

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

"TONYC " wrote in message
...
I have tried this but it still dosent take me to the first record below
the freeze pane line.

Tony

Go To Home
 

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.

--
Regards,
Tom Ogilvy

"Dana DeLouis" wrote in message
...
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

"TONYC " wrote in message
...
I have tried this but it still dosent take me to the first record below
the freeze pane line.

Tony

Go To Home
 

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

--
Message posted from http://www.ExcelForum.com

Go To Home
 

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:

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

--
Regards,
Tom Ogilvy

"TONYC " wrote in message
...
Thank you all for your response. After adding the following line to the
macro, it did appear to work to my satisfaction.

Application.Goto Range("A1"), True

Tony


---
Message posted from http://www.ExcelForum.com/