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.
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 activesheet.usedrange.select 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.
Or you could get fancy with this code.
Dim rng As Range
Set rng = Range("A1").SpecialCells(xlCellTypeLastCell)
Range(Cells(1, 1), rng).Select
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.
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.)
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.