Excel VB Commands for Control-Home & Control-End without Using SendKeys…

[Russ1701]

Does anyone know how to add the command to press Control –Home and Control-End without using SendKeys?

As many of you know, using SendKeys tends to play havoc with the NumLock key, turning it off and as a toggle.

I need to be able to go to the highest cell in the upper left outside of the frozen window panes, i.e., within the editable data, not within a frozen pane.

For Control-End, I sim0ply need to be able to go to the lowest lower-right corner cell of the worksheet that has not been used as of yet.

Thanks.

 

[Mohammad Basem]

This is a very interesting issue. I tried earlier to understand and have control over the screen dimensions and position, and found that this is not straight forward (at least part of it).

I think what you are asking for is the same, unless someone comes up with a better technique.

Meanwhile, you may use the two procedures below to go to the first (Ctrl+Home) and last (Ctrl+End) cells. Copy the code to a Module code window.

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Option Explicit

Sub GotoFirstCell()

    Dim iFirstRow As Integer, iFirstColumn As Integer

    Select Case ActiveWindow.Panes.Count
        Case 1              [COLOR="Green"]'* No frozen panes[/COLOR]
            iFirstRow = 1
            iFirstColumn = 1

        Case 2
            If ActiveWindow.Panes(1).VisibleRange.Rows.Count = 1 Then       [COLOR="Green"]'* Top row frozen[/COLOR]
                iFirstRow = 2
                iFirstColumn = 1
            Else            [COLOR="green"]'* Left column frozen[/COLOR]
                iFirstRow = 1
                iFirstColumn = 2
            End If

        Case 4              [COLOR="green"]'* Frozen rows and columns[/COLOR]
            iFirstRow = ActiveWindow.Panes(1).VisibleRange.Rows.Count + 1
            iFirstColumn = ActiveWindow.Panes(1).VisibleRange.Columns.Count + 1

    End Select
    Cells(iFirstRow, iFirstColumn).Select

End Sub

Sub GotoLastCell()
    Cells.SpecialCells(xlCellTypeLastCell).Select
End Sub[/COLOR][/SIZE][/FONT]