I have read that I should avoid using SendKeys at all cost. How else can I
get the cursor to the upper-left most cell (assume a window freeze at c3)
that would emulate the CTL + HOME keys?

The code above is not working.

Thanks.

Marco

Maybe...

Option Explicit
Sub testme()

Dim myRow As Long
Dim myCol As Long

With ActiveWindow
myRow = .SplitRow
myCol = .SplitColumn
End With

ActiveSheet.Cells(myRow + 1, myCol + 1).Select
'or
Application.Goto reference:=ActiveSheet.Cells(myRow + 1, myCol + 1), _
Scroll:=True

End Sub

But it can get uglier if you have hidden rows/columns at that frozen pane.

Marco wrote:

I have read that I should avoid using SendKeys at all cost. How else can I
get the cursor to the upper-left most cell (assume a window freeze at c3)
that would emulate the CTL + HOME keys?

The code above is not working.

Thanks.

Marco

--

Dave Peterson

There are...and the code below sends me to a hidden cell.

Here is more detail: the freeze is actually at X3. Columns L to W are
hidden and A to K are outside of the window. I need to end up at X3 this
time around. Next time it could be AG7.

I truely appreciate the help.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Sub testme()

Dim myRow As Long
Dim myCol As Long

With ActiveWindow
myRow = .SplitRow
myCol = .SplitColumn
End With

ActiveSheet.Cells(myRow + 1, myCol + 1).Select
'or
Application.Goto reference:=ActiveSheet.Cells(myRow + 1, myCol + 1), _
Scroll:=True

End Sub

But it can get uglier if you have hidden rows/columns at that frozen pane.

Marco wrote:

I have read that I should avoid using SendKeys at all cost. How else can I
get the cursor to the upper-left most cell (assume a window freeze at c3)
that would emulate the CTL + HOME keys?

The code above is not working.

Thanks.

Marco

--

Dave Peterson

I might use sendkeys in this case.

But if you want...

Option Explicit
Sub testme()

Dim myRow As Long
Dim myCol As Long
Dim myCell As Range

With ActiveWindow
myRow = .SplitRow
myCol = .SplitColumn
End With

Set myCell = Nothing
On Error Resume Next
With ActiveSheet
Set myCell = .Range(.Cells(myRow + 1, myCol + 1), _
.Cells(.rows.Count, .Columns.Count)) _
.Cells.SpecialCells(xlCellTypeVisible).Cells(1)
End With
On Error GoTo 0

If myCell Is Nothing Then
'do nothing
Beep
Else
myCell.Select
End If
End Sub

Marco wrote:

There are...and the code below sends me to a hidden cell.

Here is more detail: the freeze is actually at X3. Columns L to W are
hidden and A to K are outside of the window. I need to end up at X3 this
time around. Next time it could be AG7.

I truely appreciate the help.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Sub testme()

Dim myRow As Long
Dim myCol As Long

With ActiveWindow
myRow = .SplitRow
myCol = .SplitColumn
End With

ActiveSheet.Cells(myRow + 1, myCol + 1).Select
'or
Application.Goto reference:=ActiveSheet.Cells(myRow + 1, myCol + 1), _
Scroll:=True

End Sub

But it can get uglier if you have hidden rows/columns at that frozen pane.

Marco wrote:

I have read that I should avoid using SendKeys at all cost. How else can I
get the cursor to the upper-left most cell (assume a window freeze at c3)
that would emulate the CTL + HOME keys?

The code above is not working.

Thanks.

Marco

--

Dave Peterson


--

Dave Peterson

But the SendKeys is not working. Do I have the correct syntax? What could
be some of the variations I can use?

I'll try the code below, just thinking ahead.

Thanks again.

"Dave Peterson" wrote:

I might use sendkeys in this case.

But if you want...

Option Explicit
Sub testme()

Dim myRow As Long
Dim myCol As Long
Dim myCell As Range

With ActiveWindow
myRow = .SplitRow
myCol = .SplitColumn
End With

Set myCell = Nothing
On Error Resume Next
With ActiveSheet
Set myCell = .Range(.Cells(myRow + 1, myCol + 1), _
.Cells(.rows.Count, .Columns.Count)) _
.Cells.SpecialCells(xlCellTypeVisible).Cells(1)
End With
On Error GoTo 0

If myCell Is Nothing Then
'do nothing
Beep
Else
myCell.Select
End If
End Sub

Marco wrote:

There are...and the code below sends me to a hidden cell.

Here is more detail: the freeze is actually at X3. Columns L to W are
hidden and A to K are outside of the window. I need to end up at X3 this
time around. Next time it could be AG7.

I truely appreciate the help.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Sub testme()

Dim myRow As Long
Dim myCol As Long

With ActiveWindow
myRow = .SplitRow
myCol = .SplitColumn
End With

ActiveSheet.Cells(myRow + 1, myCol + 1).Select
'or
Application.Goto reference:=ActiveSheet.Cells(myRow + 1, myCol + 1), _
Scroll:=True

End Sub

But it can get uglier if you have hidden rows/columns at that frozen pane.

Marco wrote:

I have read that I should avoid using SendKeys at all cost. How else can I
get the cursor to the upper-left most cell (assume a window freeze at c3)
that would emulate the CTL + HOME keys?

The code above is not working.

Thanks.

Marco

--

Dave Peterson


--

Dave Peterson

I took out the space character and it worked ok:

SendKeys "(^{HOME})"



Marco wrote:

But the SendKeys is not working. Do I have the correct syntax? What could
be some of the variations I can use?

I'll try the code below, just thinking ahead.

Thanks again.

"Dave Peterson" wrote:

I might use sendkeys in this case.

But if you want...

Option Explicit
Sub testme()

Dim myRow As Long
Dim myCol As Long
Dim myCell As Range

With ActiveWindow
myRow = .SplitRow
myCol = .SplitColumn
End With

Set myCell = Nothing
On Error Resume Next
With ActiveSheet
Set myCell = .Range(.Cells(myRow + 1, myCol + 1), _
.Cells(.rows.Count, .Columns.Count)) _
.Cells.SpecialCells(xlCellTypeVisible).Cells(1)
End With
On Error GoTo 0

If myCell Is Nothing Then
'do nothing
Beep
Else
myCell.Select
End If
End Sub

Marco wrote:

There are...and the code below sends me to a hidden cell.

Here is more detail: the freeze is actually at X3. Columns L to W are
hidden and A to K are outside of the window. I need to end up at X3 this
time around. Next time it could be AG7.

I truely appreciate the help.

"Dave Peterson" wrote:

Maybe...

Option Explicit
Sub testme()

Dim myRow As Long
Dim myCol As Long

With ActiveWindow
myRow = .SplitRow
myCol = .SplitColumn
End With

ActiveSheet.Cells(myRow + 1, myCol + 1).Select
'or
Application.Goto reference:=ActiveSheet.Cells(myRow + 1, myCol + 1), _
Scroll:=True

End Sub

But it can get uglier if you have hidden rows/columns at that frozen pane.

Marco wrote:

I have read that I should avoid using SendKeys at all cost. How else can I
get the cursor to the upper-left most cell (assume a window freeze at c3)
that would emulate the CTL + HOME keys?

The code above is not working.

Thanks.

Marco

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Do you mean to get to the upper left cell of the Pane containing the active
cell? If so, try

Dim P As Pane
Dim N As Long
If ActiveSheet.Type = xlWorksheet Then
For N = 1 To ActiveWindow.Panes.Count
Set P = ActiveWindow.Panes(N)
If Not Application.Intersect(ActiveCell, P.VisibleRange) Is Nothing
Then
P.VisibleRange(1, 1).Select
Exit For
End If
Next N
End If


Otherwise, try

If ActiveSheet.Type = xlWorksheet Then
ActiveWindow.Panes(1).VisibleRange.Cells(1, 1).Select
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"Marco" wrote in message
...
I have read that I should avoid using SendKeys at all cost. How else can I
get the cursor to the upper-left most cell (assume a window freeze at c3)
that would emulate the CTL + HOME keys?

The code above is not working.

Thanks.

Marco

It would be the upper left cell under and to the left of the frozen
rows/column.

I tried both codes you provided and the second one takes me to the cell next
to the labels [the equivalent to A1 on a non-frozen sheet]. The other code
gave me an error at the "If not ...." line.

Thanks for your help.

"Chip Pearson" wrote:

Do you mean to get to the upper left cell of the Pane containing the active
cell? If so, try

Dim P As Pane
Dim N As Long
If ActiveSheet.Type = xlWorksheet Then
For N = 1 To ActiveWindow.Panes.Count
Set P = ActiveWindow.Panes(N)
If Not Application.Intersect(ActiveCell, P.VisibleRange) Is Nothing
Then
P.VisibleRange(1, 1).Select
Exit For
End If
Next N
End If


Otherwise, try

If ActiveSheet.Type = xlWorksheet Then
ActiveWindow.Panes(1).VisibleRange.Cells(1, 1).Select
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"Marco" wrote in message
...
I have read that I should avoid using SendKeys at all cost. How else can I
get the cursor to the upper-left most cell (assume a window freeze at c3)
that would emulate the CTL + HOME keys?

The code above is not working.

Thanks.

Marco