Excel: Code for Ctrl-Home

[NeoPa]

I would like to know how to select the cell at the top left of the scrolling area of a worksheet. This point is set from the menu by using Window \ Freeze Panes when the required cell is selected. After that point, pressing Ctrl-Home moves the cell selection to that same cell. It is very useful when working with large sets of data where either the rows or the columns (or both of course) have titles at the top (and/or left).

I got the code to select the cell at the last used cell of the range (Ctrl-End) from recording the keystroke into a macro and looking at the resultant code :

Selection.SpecialCells(xlLastCell).Select

Unfortunately, the code used for Ctrl-Home in the recorded macro is simply to set to the specific range selection :

Range("B2").Select

PS. If this requires enquiring of various objects then I'm happy to do that. Anything that can help me to duplicate the effect of hitting Ctrl-Home.

[ADezii]

@NeoPa
Hello NeoPa, to select the Cell at the Top Left (ULC) of the Scrolling Area of a Worksheet, you can try:

1. Programmatically enabling the SCROLL LOCK.
2. Using SendKeys, send the {HOME} Key (I know I'm redundant!) (LOL)!
3. I'm sure that you can take it from here.

[ChipR]

Cells(ActiveWindow.Panes(4).ScrollRow, ActiveWindow.Panes(4).ScrollColumn).Select

NeoPa:
It seems that the first scrollable Row and Column from Window or Pane objects always reflects the visibly available screen. This is a nuisance (and I'm still interested in a definitive and non-destructive way to determine the top & left scrollable cells), but for my current purposes I can allow the moving of the visible window to the top left anyway, and thereby get a correct result. Hardly non-destructive, but the best I was able to come up with so far (I still really appreciate the posts) :

With ActiveWindow
    If .FreezePanes Then
        'Moves the visible pane without setting these values
        'beyond their allowable bounds.
        .ScrollRow = 1
        .ScrollColumn = 1
        Call Cells(RowIndex:=.ScrollRow, _
                   ColumnIndex:=.ScrollColumn).Select
    Else
        Call Range("A1").Select
    End If
End With

[NeoPa]

Thanks a bunch chaps. That has resolved a long-standing irritation (Jumps around with glee).

With ActiveWindow
    Call Cells(RowIndex:=.ScrollRow, _
               ColumnIndex:=.ScrollColumn).Select
End With

Chip, your post was enough to tell me where to look. That helped greatly.

ADezii, Thanks for posting. I was really looking for the sort of solution I ended up with, but all suggestions are appreciated.

[NeoPa]

Hang on. It seems I may not have it exactly right yet. The ActiveWindow (and the panes) seem to refer only to the visible portion of the screen. If I set FreezePanes at B2 and scroll down many pages then the .ScrollRow reflects the top row visible on the screen rather than the top row selectable in the scrolling area. I will look some more.

[NeoPa]

It seems that the first scrollable Row and Column from Window or Pane objects always reflects the visibly available screen. This is a nuisance (and I'm still interested in a definitive and non-destructive way to determine the top & left scrollable cells), but for my current purposes I can allow the moving of the visible window to the top left anyway, and thereby get a correct result. Hardly non-destructive, but the best I was able to come up with so far (I still really appreciate the posts) :

With ActiveWindow
    If .FreezePanes Then
        'Moves the visible pane without setting these values
        'beyond their allowable bounds.
        .ScrollRow = 1
        .ScrollColumn = 1
        Call Cells(RowIndex:=.ScrollRow, _
                   ColumnIndex:=.ScrollColumn).Select
    Else
        Call Range("A1").Select
    End If
End With

[bynowhere]

they this code - it "Ctrl-Home" all sheets and closes all groupings...

Private Sub Workbook_Open() 
     ' group and Ctrl+Home all sheets at file open
    Dim ws As Worksheet 
    Application.ScreenUpdating = False 
    For Each ws In ActiveWorkbook.Worksheets 
        ActiveWorkbook.Sheets(ws.Name).Activate 
        ActiveWorkbook.Sheets(ws.Name).Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 
        With ActiveWindow 
            .ScrollRow = 1 
            .ScrollColumn = 1 
            If .FreezePanes Then 
                Call Cells(RowIndex:=.ScrollRow, ColumnIndex:=.ScrollColumn).Select 
            Else 
                Call Range("A1").Select 
            End If 
        End With 
    Next 
    Sheets("StartSheet").Select 
    Application.ScreenUpdating = True 
End Sub
 

[NeoPa]

Interestingly, though the last post was fundamentally a duplication of logic and code already posted, it got me to thinking again, and I was able to produce some neater code that handles this. It works on the basis that the .ScrollRow and .ScrollColumn properties of the ActiveWindow cannot be set below the values of the FreezePanes settings. Thus it is not necessary to check for .FreezePanes in the code even. It works in either scenario :

With ActiveWindow
    .ScrollRow = 1
    .ScrollColumn = 1
    Call Cells(.ScrollRow, .ScrollColumn).Select
End With

Lines #2 and #3 cause the settings to be the minimum allowed in any circumstances. If FreezePanes is set to something then these parameters will be reflected. Otherwise the values set will hold. In either case it will be correct :-)