Select to Bottom of Array in Excel VBA: Safe Methods

VBA & macros · Updated November 21, 2026

To select from the active cell to the bottom of its contiguous data block in Excel VBA, use Range(ActiveCell, ActiveCell.End(xlDown)).Select—but only when the cell below is part of the block and there are no gaps. For a reliable selection to the last populated row or to the worksheet’s final row, use a method suited to that specific meaning of “bottom”; a VBA array in memory cannot itself be selected as worksheet cells.

What “bottom of the array” can mean

Excel users often call a column or table of worksheet values an “array,” but VBA distinguishes worksheet ranges from arrays stored in memory. A range such as A2:A20 consists of cells on a worksheet. A VBA array such as values(1 To 19, 1 To 1) is data held in the macro’s memory. You can select worksheet cells, but you cannot highlight an in-memory array on the sheet until you write its contents to a range.

Before choosing code, identify the destination you actually need:

  • Bottom of a continuous block: stop at the first blank cell or the last nonblank cell before a gap, depending on the starting position.
  • Last populated row in a column: find the final cell with content in that column, even if there are gaps above it.
  • Bottom of the worksheet: extend the selection to the last available row, whether or not it contains data.
  • Bottom of an in-memory array: use the array’s bounds in VBA, then process the values or output them to cells.

These goals are not interchangeable. Selecting a million worksheet rows when you only need the records can slow later operations and make code harder to maintain.

Select to the bottom of a contiguous block

End(xlDown) behaves like pressing Ctrl+Down in Excel: it moves to the edge of the current region in the chosen direction. From a populated cell, it generally finds the last filled cell before a blank, or the next filled cell after a blank. That second behavior is why it can jump over the result you expected when the starting cell is empty.

The following macro selects from the active cell to the bottom edge of its current region in the same column. Save a copy of the workbook before running a macro; VBA actions cannot be undone with Ctrl+Z.

Sub SelectDownCurrentBlock()
    Dim ws As Worksheet
    Dim firstCell As Range
    Dim lastCell As Range

    ' Use the active cell and its worksheet.
    Set ws = ActiveSheet
    Set firstCell = ActiveCell

    ' Move to the edge of the contiguous region below.
    Set lastCell = firstCell.End(xlDown)
    ws.Range(firstCell, lastCell).Select
End Sub

For example, if A2:A8 contains values and A9 is blank, running this from A2 selects A2:A8. If A2 is blank and A3:A8 contains values, the jump can land in the next populated region instead; it does not mean “find the final used row in the column.” If the first cell is already at the bottom of a block, the selection may be just that cell.

To run it, click the starting cell, press Alt+F11 on Windows to open the Visual Basic Editor, insert the procedure in a standard module, and run it. On Mac, open the Visual Basic Editor from the Developer tab or Tools > Macro > Visual Basic Editor; shortcut availability depends on the Mac keyboard and Excel settings. You can also run a macro from Developer > Macros in Excel on Windows. Select the macro and choose Run.

Select from a cell to the worksheet’s last row

If “bottom” means the physical bottom of the worksheet, use the row count of the worksheet rather than searching for data. A modern Excel worksheet has 1,048,576 rows. This example selects from the active cell down to the last row in the same column:

Save a copy before running this macro. It selects cells only; it does not change their contents, but macro actions cannot be reversed with Ctrl+Z.

Sub SelectToSheetBottom()
    Dim ws As Worksheet
    Dim firstCell As Range
    Dim bottomCell As Range

    ' Keep the selection in the active cell's column.
    Set ws = ActiveSheet
    Set firstCell = ActiveCell
    Set bottomCell = ws.Cells(ws.Rows.Count, firstCell.Column)

    ws.Range(firstCell, bottomCell).Select
End Sub

This is useful when you need to apply an operation to every row from the starting point down, including currently blank rows. It is usually not the right choice for copying or processing a short data list: selecting over a million cells can make subsequent steps inconvenient and may increase the amount of work a macro performs. To select to the bottom of a particular column, use that column’s final row number, for example ws.Cells(ws.Rows.Count, 1) for column A.

Find the last populated row, including gaps

For a list that may contain blank cells between records, find the last populated cell in the relevant column. The common End(xlUp) pattern starts at the bottom of that column and moves upward to the first nonempty cell. It does not stop at a gap near the top.

With data in column A starting at row 2 and a header in A1, this macro selects A2 through the last populated row. Save a copy of the workbook before running it; selection-changing macros cannot be undone with Ctrl+Z.

Sub SelectColumnADataToLastRow()
    Dim ws As Worksheet
    Dim lastRow As Long

    ' Find the last nonempty cell in column A.
    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ' Select data rows only when at least one exists below the header.
    If lastRow >= 2 Then
        ws.Range(ws.Cells(2, 1), ws.Cells(lastRow, 1)).Select
    End If
End Sub

If A2:A5 and A8 contain values, this selects A2:A8, including the blank cells in between. If column A has no data beneath its header, End(xlUp) can return row 1, so the guard prevents an invalid data-range selection. Change the column number from 1 to another number when needed: A is 1, B is 2, and so on.

There is an important distinction between “last row in column A” and “last row anywhere on the sheet.” If column A is blank for a record but another column has a value, the column-A method will not find that record. Choose a column that is reliably populated, such as a record ID, or search the whole used content area when no single column is dependable.

Choose the method that matches the data

Use End(xlDown) for a known, uninterrupted block. Use End(xlUp) from the bottom of a column when the list can contain gaps but one chosen column identifies records. Use the worksheet row count when you truly need the physical bottom, not the last record. These methods answer different questions, so replacing one with another can produce a plausible-looking but incorrect selection.

For data spread across multiple columns, finding the last used row anywhere on a worksheet can be done with Find. The search settings should be stated explicitly because Excel can remember some Find dialog settings from earlier searches. A result of Nothing means the sheet has no matching cell.

Sub SelectRowsThroughLastUsedCell()
    Dim ws As Worksheet
    Dim lastCell As Range

    ' Search all cells for the last row containing a value or formula.
    Set ws = ActiveSheet
    Set lastCell = ws.Cells.Find(What:="*", _
        After:=ws.Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
        MatchCase:=False, SearchFormat:=False)

    If Not lastCell Is Nothing Then
        ws.Range(ws.Cells(1, 1), lastCell).Select
    End If
End Sub

This selects a rectangle from A1 to the last cell found by row order, not just the cells that contain data. Because LookIn:=xlFormulas searches formulas as well as constants, a formula that displays an empty string still counts as a used cell. If you need the last row with a visible, nonempty result, that requires a different test. Also, “used range” can be affected by formatting or prior editing; the Find approach searches for content or formulas rather than merely formatted cells.

Work with a VBA array instead of selecting cells

If you literally mean the bottom element of a VBA array, there is no worksheet selection to make. Use LBound and UBound to find the array’s bounds. For a one-dimensional array, the final index is UBound(values). For a two-dimensional array, specify the dimension: UBound(values, 1) gives the upper bound of dimension one, while UBound(values, 2) gives the upper bound of dimension two.

For example, a two-dimensional array loaded from a worksheet range commonly uses dimension 1 for rows and dimension 2 for columns. To process its last row, use the upper bound of the first dimension. An array’s lower bound is not always 1, so code that assumes the first item is at index 1 can fail for arrays declared with another lower bound or populated by certain VBA functions.

If you want the array displayed in Excel, assign it to a range of matching dimensions rather than trying to select the array. A two-dimensional array can be written to a same-sized range in one assignment. The worksheet range must have enough rows and columns for the array, and the array should be sized consistently with the destination. Writing values to a range does not select or format those cells automatically.

Selection, editing, and safer alternatives

VBA can select only cells on the active worksheet. A range must be activated or selected on its sheet before a selection-dependent action can work, which is one reason fully qualifying worksheet references matters. Code that uses ActiveCell also depends on what the user clicked before running it; for a repeatable macro, consider naming the worksheet and defining a fixed starting cell instead.

Many macros do not need to select anything. Directly assign, inspect, or clear a range instead of selecting it first. For instance, a procedure can set a Range variable and work with its Value property. This reduces dependence on the active sheet and avoids flashing selections across the screen. Selection is appropriate when the user should visibly see the target or when an Excel operation specifically requires an active selection, but it is not a prerequisite for ordinary VBA range operations.

For a manual selection, click the starting cell and press Ctrl+Shift+Down on Windows to extend through the current data region in that direction. On Mac, Command+Shift+Down is the usual corresponding shortcut, though keyboard settings and Excel versions can affect shortcuts. The keyboard action follows data-region boundaries; it does not guarantee selection to the last used row or the physical bottom of the worksheet.

Common errors and how to avoid them

  • The selection stops too early: there is a blank row in the data. Use a last-row search in a reliable key column instead of End(xlDown).
  • The selection jumps far down: the starting cell is blank, so Excel moves to the next data region. Start from a known populated cell or use a last-row method.
  • It includes too many rows: the macro selects to Rows.Count. Use that only for the actual worksheet bottom; otherwise calculate the last data row.
  • It misses records: the column used to find the last row has blanks at the end. Pick a consistently populated identifier column or search across the sheet.
  • A macro fails with a range or selection error: the code may be referring to a different worksheet than the active one. Qualify both the worksheet and its cells, and make sure any required sheet is active before selecting.

Before running code that changes data, test it on a copy and verify the selected address. For a quick check, the Immediate window in the Visual Basic Editor can display a range’s address, or the macro can show it in a message box. A selection itself does not alter cell values, but later steps in the same macro might, and Ctrl+Z will not undo VBA changes.

Frequently Asked Questions

How do I select from the active cell to the last used row in Excel VBA?
Use End(xlUp) from the bottom of a dependable data column to find its last populated row, then select from your starting cell to that row. If the last record may have a blank in that column, search another reliable column or search the sheet.

Why does Range(ActiveCell, ActiveCell.End(xlDown)).Select select the wrong cells?
End(xlDown) follows contiguous-region navigation. A blank starting cell or an empty row inside the data changes where the jump ends. It does not mean “find the last nonblank cell anywhere below.”

How do I select all rows to the bottom of a worksheet?
Use ws.Cells(ws.Rows.Count, columnNumber) as the endpoint, where columnNumber is the starting cell’s column. This reaches the final worksheet row, not the last row containing data.

Can VBA select the last element of an array?
No worksheet selection applies to an array stored in VBA memory. Get its final index with UBound and process that element, or write the array to a suitably sized worksheet range if you want to see its values in cells.

Can I do this without VBA?
Yes. On Windows, Ctrl+Shift+Down extends a selection to the edge of the current data region. It is quick for continuous data, but it does not solve gaps or identify the last used row across multiple columns.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.