Select Actual Used Range in Excel worksheet

To totally unlock this section you need to Log-in

Login
It's easy to select the current range in Excel – just press Ctrl + A. That shortcut selects all the cells in the block that surround the active cell. The selection stops at the first blank row and blank column.

In this example, cell A1 was active, and range A1:A2 was selected with the keyboard shortcut.

Select All Cells

If you use the Ctrl + A shortcut while an isolated or empty cell is selected, then the entire worksheet is selected. In the screen shot below, cell E2 was active when the shortcut was used. The entire sheet was selected.

If the Ctrl + A shortcut selects a range, you can press Ctrl + A again, to select the entire sheet. In the screen shot below, cell A1 was active, and by pressing Ctrl + A twice, the entire worksheet is selected.

Select Filled Cells Only

To select only the filled cells on a worksheet, you can use the Find dialog box.

On the Excel Ribbon's Home tab, click Find & Select, then click Find (or use the

keyboard shortcut -- Ctrl+F)

Type an asterisk (*) in the "Find what" field

Click the "Find All" button

Press Ctrl+A to select all the ranges in the list

Click Close. Only the filled cells on the worksheet are selected.

Select the Used Range

To select all the cells in the used range on a worksheet, you can use the following shortcut sequence:

Press Ctrl + Home, to select cell A1

Press Ctrl + Shift + End, to select all cells from A1 to the last used cell.

Select the Actual Used Range

As you can see in the screenshot above, there is nothing visible in cell D8, but it is included in the selected used range. Perhaps there was a value in that cell, and it was deleted, or the cell is formatted.

[tweet]
If you want to select only the cells in the actual used range, you can use this macro instead. The macro was written by Rick Rothstein, and looks for the last cell with a value, and ends the selection there.

Note: This code ignores cells with formulas that are displaying the empty string. If you need to identify formula cells that might be displaying the empty string, then change the xlValues argument to xlFormulas.

Sub PickedActualUsedRange()


  Range("A1").Resize(Cells.Find(What:="*", SearchOrder:=xlRows, _


      SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _


      Cells.Find(What:="*", SearchOrder:=xlByColumns, _


      SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Select


End Sub

After running the PickedActualUsedRange macro, the selection ends at cell D7, instead of D8.

Select from First Cell With Data

Usually, selecting from cell A1 won't hurt anything, but if you really want the actual used range on the worksheet, then you would need to use a macro like this one...

Sub SelectActualUsedRange()


  Dim FirstCell As Range, LastCell As Range


  Set LastCell = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _


      SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _


      Cells.Find(What:="*", SearchOrder:=xlByColumns, _


      SearchDirection:=xlPrevious, LookIn:=xlValues).Column)


  Set FirstCell = Cells(Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlRows, _


      SearchDirection:=xlNext, LookIn:=xlValues).Row, _


      Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlByColumns, _


      SearchDirection:=xlNext, LookIn:=xlValues).Column)


  Range(FirstCell, LastCell).Select


End Sub

Another Simple Macro to select Used Range

Another very simple solution in VBA (Macro) to select only used range (both values and formulas with empty strings) in a worksheet is the following:

Sub SelectAll()


Worksheets("Sheet1").Activate


ActiveSheet.UsedRange.Select


End Sub