Select Actual Used Range in Excel Sheet

Select Actual Used Range in Excel Sheet

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. But how can you select actual used range in Excel sheet?

Select Current Range

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

selectusedrange01

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.

selectusedrange02

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.

selectusedrange03

Select Filled Cells Only

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

  1. On the Excel Ribbon’s Home tab, click Find & Select, then click Find (or use the keyboard shortcut — Ctrl+F)
  2. Type an asterisk (*) in the “Find what” field
  3. Click the “Find All” button
  4. Press Ctrl+A to select all the ranges in the list
    • selectusedrange04
  5. Click Close

Only the filled cells on the worksheet are selected.
selectusedrange05

Select the Used Range

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

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

selectusedrange06

Select the Actual Used Range

As you can see in the screen shot 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.

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.

selectusedrange07

Select from First Cell With Data

[Thanks to Jelle-Jeroen Lamkamp for his comment that the first used cell might not be A1. Here is Rick’s code to deal with that situation.]
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

More Worksheet Code Examples

You can find this sample code, and a other examples, on Rick Rothstein’s Excel VBA – Worksheet page, on the Contextures website.
________________

30 thoughts on “Select Actual Used Range in Excel Sheet”

  1. I guess Rick wants A1 always selected otherwise he would have used ActiveSheet.UsedRange.Select
    This could also be a solution if he want to do that
    Sub ActiveSheetUsedRange()
    Dim sURA As String
    sURA = ActiveSheet.UsedRange.Address
    ActiveSheet.Range(“A1” & Mid(sURA, InStr(1, sURA, “:”))).Select
    End Sub

    1. @Jelle-Jeroen, thanks for posting your solution.
      Rick’s code is useful when the used range includes extra rows or columns that aren’t really used. Sometimes, if you press Ctrl + End, you’ll find that the last cell in the Used Range is way out in an empty region of your worksheet. Rick’s code would ignore those extra cells, and only select from A1 to the last cell that contains data.

    2. As Debra has mentioned, UsedRange does not always return the true used range on the worksheet. However, you do raise a good point about my code assuming the start of the used range is cell A1… that, of course, is not always true. 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
      
  2. Debra, I also like Select Visible Rows. I use it when I want to delete many rows from a filter selection. Choose your filter, select all visible rows, unfilter, delete rows. If you don’t do this and have a hundreds or thousand of rows to delete, it can take 10 minutes to delete them (if it doesn’t crash first). Select Visible Rows is hidden on “other commands”

  3. Weird…I use Ctrl+A all the time in MS Word, but when MS Excel, for some reason I use Ctrl+Shift+Spacebar to the select the current region. It seems to be nearly identical to Ctrl+A.

  4. Jelle-Jeroen is correct though as UsedRange in VBA will evaluate the used range each time you invoke it thus not selecting cleared cells on the edges of the area. Don’t know if it’s any better or worse to use than Rick’s code.
    Dom

  5. @Domski, in Excel 2010, if I format cell E10 with white font, then change it back to No Fill, that cell is included in the selected range, using Jelle-Jeroen’s code. With Rick’s example, it isn’t selected.

  6. While it is covered in a note on the webpage Debra was kind enough to setup for some of the code I have posted across the years, I just wanted to emphasize it here for those of you who have not visited that webpage…
    All of my “last whatever” code posted in this blog article ignores cells with formulas that are displaying the empty string. In other words, the code only identifies the “last whatever” for visibly displayed values (no matter if they are constants or the result of formulas)… that means there could be cells containing formulas (displaying the empty string) located past the identified “last whatever”. If you need to identify the absolute “last whatever” containing data or formulas (no matter what that formula is displaying), then change the xlValues assignment to xlFormulas for the all of the LookIn arguments.

  7. I’m not sure why you wouldn’t use something simple to select the used range on a worksheet, like:
    ActiveSheet.UsedRange.Select
    or if you want to get fancy:
    Sub SelectUsedRange()
    Dim rng As Range
    Set rng = Range(“A1”).SpecialCells(xlCellTypeLastCell)
    Range(Cells(1, 1), rng).Select
    End Sub

  8. @Gregory,
    I am guessing you did not read the entire thread as this was covered elsewhere. Very quickly… go to a blank sheet and select cell A1, then press and hold the CTRL key while pressing some other key (say, for example, J10). Okay, you should have two cells selected… change their Fill Color to, say White, and then change it Immediately back to No Fill. Okay, what is the UsedRange. Looking at the sheet, you might say “nothing”. Type this line of code into the Immediate Window and execute it…
    ? ActiveSheet.UsedRange.Address, Cells.SpecialCells(xlCellTypeLastCell).Address
    Does the print out match your expectations? There are other ways to get UsedRange or SpecialCells’ xlCellTypeLastCell (which uses the UsedRange, by the way) to return an incorrect range… the method I proposed does not suffer from this defect.

  9. My apologies. I was commenting on the used range, not the actual used range as defined by having some value in a cell, which can be a vexing problem at times. Your code works wonders if there is an actual value in the range.

  10. Very useful information. I like the fact that you caught that front end of the range needed trimmed also instead of starting from cell A1. There are two things I noticed that I think the code needs. First, whenever the Find command is used if nothing is returned then an error occurs, so there needs to be some error handling. Secondly, this code has far more versatility if you use it as a function so that it can be used on any worksheet instead of just on the active worksheet. I modified the code some so it will do both.

    Sub TestRange()
        ActualUsedRange(ActiveSheet).Select
    End Sub
    Function ActualUsedRange(MySheet As Worksheet) As Range
        Dim FirstCell As Range, LastCell As Range
        'Go to the ErrorHandler line if an error occurs such as no data in the worksheet
        On Error GoTo ErrorHandler
        With MySheet
            'Find the last cell
            Set LastCell = .Cells(.Cells.Find(What:="*", SearchOrder:=xlRows, _
                SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
                .Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, LookIn:=xlValues).Column)
            'Find the first cell
            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)
            'Set what the actual range is
            Set ActualUsedRange = .Range(FirstCell, LastCell)
        End With
        'Exits the function so the error handler isn't called every time
        Exit Function
    ErrorHandler:
        'Sets the range to cell A1 of the worksheet if no data is in the worksheet
        Set ActualUsedRange = MySheet.Range("A1")
    End Function
  11. Simply amazing.
    I dont know how it does it exactly but i converted it into C# and it worked perfectly for me. Thanks a million.
    Excel.Range last2 = range.Cells[range.Cells.Find(What: “*”, SearchOrder: Excel.XlRowCol.xlRows,
    SearchDirection: Excel.XlSearchDirection.xlPrevious, LookIn: Excel.XlFindLookIn.xlValues).Row,
    range.Cells.Find(What:”*”, SearchOrder:Excel.XlRowCol.xlColumns, SearchDirection: Excel.XlSearchDirection.xlPrevious,
    LookIn: Excel.XlFindLookIn.xlValues).Column];

  12. Thank you for posting this. Used this today, from Access with automation. I modified it to be a function returning the range, when being passed a worksheet, and needed to explicitly say which worksheet the Cells call had to work on, otherwise it can fail during automation if multiple excel are open.
    Public Function GetActualUsedRange(ws As excel.Worksheet) As excel.Range
    Set GetActualUsedRange = ws.Range(“A1″).Resize(ws.Cells.Find(What:=”*”, SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
    ws.Cells.Find(What:=”*”, SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Column)
    End Function

  13. I know this an old post but…..
    I have one more twist. I am filtering a table to select the data I need to copy and paste to another worksheet. I need to be able to select all the data from the current cursor location to the end of actual data. That may be one to 50 rows with a constant number of columns (9).
    Currently there are two rows of data;
    A2514 – I2514
    A2516 – I2516
    Any ideas? I’m thinking I’m close with Rick’s solution but I need to start from current cursor location.

  14. Hello,
    Thank you for posting this thread, found it very useful 🙂 I have one problem though.
    The code works well only once. I created a word form and users can upload their excel tables by clicking a command button. This works once but when I push the command button for second time (to add a second excel table) the vba gives an error text “Run-time error ’91’: Object variable or With block variable not set”. My code is this:
    ————————————-
    Private Sub Commandbutton_Click()
    Dim intChoice As Integer
    Dim strPath As String
    ‘only allow the user to select one file
    Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
    ‘make the file dialog visible to the user
    intChoice = Application.FileDialog(msoFileDialogOpen).Show
    ‘determine what choice the user made
    If intChoice 0 Then
    ‘get the file path selected by the user
    strPath = Application.FileDialog( _
    msoFileDialogOpen).SelectedItems(1)
    End If
    ‘ The function below receives as input a file path and automates that excel workbook
    Dim objExcel As Object
    Dim objWorkbook As Object
    Set objExcel = CreateObject(“Excel.Application”)
    objExcel.Visible = True
    Set objWorkbook = objExcel.Workbooks.Open(strPath)
    ActiveSheet.UsedRange.Copy <— Debugger said the problem is here
    'move the cursor to the bookmark
    Selection.GoTo What:=wdGoToBookmark, Name:="Bookmark"
    Selection.Paste
    ' Close Excel bits
    Excel.Application.Quit
    End Sub
    ——————————————————-
    Please, help?

    1. I suppose you need to activate the sheet like this:
      Worksheets(1).Activate
      Instead of (1) you may use (“Sheet1”) or another number if appropriate.
      Insert the instruction just before
      ActiveSheet.UsedRange.Copy
      Good luck.

  15. Success!
    I still had to insert the Workbook -element into the code, so the following code worked:
    objWorkbook.Worksheets(1).Activate
    objWorkbook.ActiveSheet.UsedRange.Copy
    Thank you so much TheoDeed for the help!

  16. How do I use an assigned cell for the button. If a button is pressed then only the selected cell will show the item. and not the selected cell on the entire worksheet

  17. How do you ride the range? In other words, how do you change the focal point of the anchor when you want to copy and paste within a range column(s) or row(s)? Or what are some of the best approaches for copy and paste inside a range?

  18. I know this is a very old thread and I am using the code proposed by Ron for years but was still struggling with following:
    – Ron’s method exludes filtered rows and is not limited to the selected range.
    – The SpecialCells method includes the filtered rows but might show gaps, meaning the result is maybe not a continguous range.

    Following should do this:

    Function MyActualUsedRange(Optional strSheet As String, Optional R As Range) As Range
    Dim rngFirstCell As Range, rngLastCell As Range

    If strSheet = “” Then strSheet = ActiveSheet.Name
    If R Is Nothing Then Set R = Sheets(strSheet).Cells

    GetActualUsedRange:
    With Sheets(strSheet)
    Set rngLastCell = .Cells(R.Find(What:=”*”, SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
    R.Find(What:=”*”, SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Column)
    Set rngFirstCell = .Cells(R.Find(What:=”*”, After:=rngLastCell, SearchOrder:=xlRows, _
    SearchDirection:=xlNext, LookIn:=xlValues).Row, _
    R.Find(What:=”*”, After:=rngLastCell, SearchOrder:=xlByColumns, _
    SearchDirection:=xlNext, LookIn:=xlValues).Column)

    Set MyActualUsedRange = .Range(rngFirstCell.Address & “:” & rngLastCell.Address)
    End With

    Set rngFirstCell = Nothing
    Set rngLastCell = Nothing

    End Function

    Of course a Union with SpecialCells is needed.

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.