Select Excel’s Used Range on a Mac

by Gregory on March 20, 2012

I recently read a good blog post over at Contextures about selecting the actual used range on an Excel sheet, both manually and with VBA. However, using Excel on a Mac makes you keenly aware that there’s no Home button.

The used range on a worksheet starts with cell A1 and ends with the last used cell in the worksheet. This “last cell” is not always apparent, but easily found. Just use the keyboard shortcut CONTROL + G to bring up the Go To dialog box.

Go To dialog box
Click Special… which will bring up the Go To Special dialog box.
Go To Special dialog box
Select Last cell and click OK.

The last cell may sometimes surprise, because Excel considers cell formatting as being “used” so you may see blank cells that are way outside your data range. Tip: Sometimes you can delete the seemingly extra rows and columns outside your data range and it will reduce the file size.

Select the Used Range by Navigating Back Home

Once you find the last cell, you can then hold the Shift key down and click cell A1 to select the entire range. Of course if you can’t see cell A1 in the current window there is no Home button on the Mac to help you out. (Major bummer)

The next best thing is to hold the COMMAND + Shift keys down while you tap the left arrow and up arrow keys until you reach cell A1. This can be simple, or time-consuming depending upon size and shape of your worksheet.

Selecting the Used Range

To select the entire used range with VBA is a simple matter. Choose Tools > Macro > Visual Basic Editor, then choose View > Immediate Window, and type inside the immediate window and hit enter.

Create a Macro to Select the Used Range

You can also create a macro to select the entire used range by opening the VBA Editor, inserting a Module, and entering the following code.

Sub ActiveSheetUsedRange()
End Sub

Or you could get fancy with this code.

Sub SelectUsedRange()
Dim rng As Range
Set rng = Range("A1").SpecialCells(xlCellTypeLastCell)
Range(Cells(1, 1), rng).Select
End Sub

Add a Keyboard Shortcut for the Macro

To make things simpler to run the macro, you can enter a shortcut. Just choose Tools > Macro to bring up a dialog box.

Macro dialog box

To add a shortcut, select a macro and click Options… which will bring up the Macro Options dialog box.

Enter a shortcut key by clicking inside the Shortcut key box and pressing a key on the keyboard. I pressed the “u” key on the keyboard, and consequently the keyboard combination is shown as Option + Cmd + u, as you can see in the screen shot. (Depending on the key, you may also include COMMAND, Control, Shift into your shortcut.)

Macro Options dialog box

Enter a description if you wish, and then click OK. Select the Cancel button on the Macro dialog box to make it disappear.

Now you can run the macro by simply using the keyboard shortcut Option + Cmd + u.

Note: This macro will not work if a Chart sheet is selected.

Used Range verses Actual Used Range

The actual used range might be different than the used range. Meaning that some blank cells that are formatted might be included in the used range. Most likely you will only want to deal with a range that has some actual values. This would be the actual range.

Please refer to the aforementioned blog post over at Contextures to see a couple of different examples of code that you can use to select the actual used range. These examples are short and use the VBA FIND function to get the job done.

Related Posts Plugin for WordPress, Blogger...
Bill Benson August 24, 2012 at 9:47 am

Gregory, have you developed a list of things to keep in mind developing with VBA for Mac-Excel versus Win-Excel? I suppose Win32 API calls are out! Anything that can be used for substitutes? Soes most of everything else work the same, and is the Excel object model the same?

Gregory August 25, 2012 at 1:40 pm

No VBA list to speak of to date. Any ActiveX controls (button, radio button, etc.) will not work on a Mac and have to be replaced by Form controls. Most of the VBA environment is similar, but different enough it feels strange. Most of the VBA code is the same, but there seems to be some properties that don’t exist on the MAC’s version of VBA. I haven’t used VBA on the MAC enough as I still have a Windows computer at at work. Still, it would be nice if Microsoft would make the MAC version as much like the Windows version as possible in their next software update.

Comments on this entry are closed.

Previous post:

Next post: