One of my all-time favorite keyboard shortcuts in Excel is CTRL+Home, but on a Mac there is no Home button. Hence a constant source of frustration these last two years.
I finally decided to do something about that and recently figured out a solution using VBA and the Personal Macro Workbook. But before I go straight to the answer, let me tell you how I got there.
I knew that VBA was going to enter into the equation, so I started to record a macro on a Windows PC while using the Control+Home keyboard shortcut. What I found out is that Excel does not record that keyboard shortcut. Nothing, nada, zip.
I then noticed that Control+Home acted differently, depending if the sheet panes were frozen or not. When sheet panes are not frozen, the Control+Home shortcut took me to cell A1. When the sheet panes are frozen, then the upper left corner cell of the window was selected.
To make a long story short, I found out that the active window’s scroll row and column were being selected when the CTRL+Home shortcut is executed in Excel for Windows.
The Control+Home Macro
I also knew that if a Chart sheet were selected the macro would fail, so I crafted the following macro to mimic the CTRL+Home shortcut on a Mac. (Works on a Windows PC too.)
' Make sure the active sheet is a worksheet,
' then locate the active window's scroll row and
' column, and activate that cell.
Dim lngRow As Long
Dim lngCol As Long
If ActiveSheet.Type = xlWorksheet Then
lngRow = ActiveWindow.ScrollRow
lngCol = ActiveWindow.ScrollColumn
The next thing I did was create a shortcut for the macro, but realized that the macro would only work with the current workbook. Rats!
I wanted the macro work automatically on every Excel file so I chose to store this macro in the Personal Macro Workbook, which solved the problem.
However the shortcut key combination I assigned did not work in Excel for Mac. There was a conflict. It seems that COMMAND+OPTION+H is a reserved keyboard shortcut for the Mac. I found out this shortcut hides all windows except the one that is active.
How I Created the Control+Home Keyboard Shortcut on my Mac
Here are the steps I took, with a few false starts along the way.
I got a warning that the keyboard shortcut Option+Command+h was reserved.
So I chose g instead and clicked OK.
Now here is the important part so pay attention. You have to save changes to the Personal Macro Workbook. You will be asked to do this when you Quit Excel.
Run the GoHome Macro
Now lets check it out. Open Excel 2011 and select any cell that is not A1, then use the shortcut (mine was Command+Option+g) and watch the active cell change to cell A1.
If your shortcut doesn’t work you can set it now. Choose Tools > Macro > Macros… and select the GoHome macro, then click Options. Type in a shortcut key and click OK.
Now choose any cell in the top left quadrant of the current window, like C5. Choose Window > Freeze Panes. Next select any cell except C5 and run the shortcut combination for the macro, and watch the curser jump to cell C5.
The Personal Macro Workbook will load each time you open Microsoft Excel so it’s always in the background, and you don’t get that annoying “enable macro” pop-up screen.
Santhosh Shetty says
Hii i got time in the format 5.30 PM. I want to convert time in 24hr format. Pls help me.
Thanks & Regards
Cool macro but actually, you don’t need to do any of that. Macs have always had a home button. It is hidden on the newer (smaller) keyboards in the sense that you need to use the Fn key to get to it.
What you have done in the macro can be done with
Also CMD + Fn + Right Arrow goes to the right most and down most cell i.e. the last cell in a sheet.
You can combine those with Shift to highlight stuff
Yeah I figured that out and wrote another post to correct my ignorance.