Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    VBA equivalent of CTRL+HOME

    I would like include cursor movement in a macro so that the visible range of the worksheet resets and places the cursor in a specific cell. The macro would start above and left of the freeze-point of frozen panes and then end up down and right of the freeze-point.

    When I recorded the manual steps the macro shows:
    ActiveCell.SpecialCells(xlLastCell).Select
    Range("J19").Select

    I would rather not use an absolute cell reference like J19. In fact, to get there I used the key combo CTRL+HOME.

    Whats the VBA equivalent of CTRL+HOME?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about this?

    Steve

    Code:
    Sub CtrlHome()
      Application.Goto Range("A1"), True
      ActiveWindow.VisibleRange(1, 1).Select
    End Sub

  3. #3
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts
    Steve,

    A1 may not necessarily be the equivalent of CTRL+Home if panes have been frozen, it could quite easily be something like B5. I would like to know the VBA equivalent too as I use the frozen panes feature quite a lot on my larger worksheets.
    Maria
    Simmo7
    Victoria, Australia

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Something like:
    Code:
    Sub scrollpane()
       With ActiveWindow
          .ScrollRow = 1
          .ScrollColumn = 1
          .ActivePane.VisibleRange.Cells(1).Select
       End With
    End Sub
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Did you try the code. There are 2 lines to it....

    The 2nd line without going to A1 first, would get you to the upper left part of the pane in the current window, not the one at the start of the worksheet

    Steve

  6. #6
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts
    Steve, Thanks for that I confess I didn't look at line 2. This code will be very useful for me, as I have a lot of very large spreadsheets with the first few rows frozen so that users always see the header rows.
    Maria
    Simmo7
    Victoria, Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •