VBA & Macro Problems

Excel VBA: Select a Range Through the Last Used Row

Huma Ashraf ChHuma Ashraf Ch Nov 18, 2026 868 views

Question details

Select cells from the active cell through the last used row when the target column contains blank cells.

Select a Range Through the Last Used Row in Excel VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A VBA macro needs to select a range such as B1:B13, even though column B contains gaps.
Observed behavior
ActiveCell.End(xlDown) can stop at the first blank cell instead of reaching the intended final row.
Before you start

Identify a column that reliably contains data through the final row, or decide whether the last used row anywhere on the worksheet is the correct endpoint. Open the workbook in Excel and ensure you can run macros from a trusted source.

Solution 1Recommended

Find the Last Row Using a Reliably Populated Column

Use this method when column A contains data through the final row of the range you want to select.

The macro searches upward from the bottom of column A, then selects cells in the active column from the active cell to that row. Blanks in the active column do not interrupt the selection.

1
Open the VBA editor

In Excel, open Developer > Visual Basic, then choose Insert > Module.

2
Add the macro

Paste this code into the module: Sub SelectTillLast() Dim lastRow As Long With ActiveSheet If Application.WorksheetFunction.CountA(.Columns("A")) = 0 Then Exit Sub lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row If ActiveCell.Row > lastRow Then Exit Sub .Range(.Cells(ActiveCell.Row, ActiveCell.Column), _ .Cells(lastRow, ActiveCell.Column)).Select End With End Sub

3
Run the macro

Return to the worksheet, select the starting cell, such as B1, and run SelectTillLast from Developer > Macros.

Find the Last Row Using a Reliably Populated Column
Check the reference column: Column A must extend to the intended final row. If it contains unrelated data farther down, the selection will extend to that row as well.
Free Microsoft Office alternative

Work With Excel Spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar interface for opening and editing spreadsheets. Its Excel format support helps you move existing files into WPS, although Excel VBA macro support and behavior can vary by version and environment.

Open and edit Excel-compatible XLSX and XLS spreadsheetsWork with ranges and worksheet data in WPS SpreadsheetsUse a familiar spreadsheet interface for an easier transitionUse a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does End(xlDown) stop before the last row?

End(xlDown) follows the current data region and can stop at a blank. Instead, find the last row by searching upward in a reliable column with Cells(Rows.Count, "A").End(xlUp), or search the worksheet with Cells.Find.

How do I select B1 through the last row found in column A?

Select B1 before running the reference-column macro. It finds the last used row in column A and selects from B1 to that row in column B.

What happens if the active cell is below the last used row?

Both macros check the active cell's row and exit without changing the selection if it is below the detected last row. Select a starting cell at or above the endpoint before running the macro.

Can Find include formulas that display blank results?

Yes. The worksheet-wide macro uses LookIn:=xlFormulas, so a formula may be found even when its displayed result is an empty string.

How do I keep the VBA macro in my workbook?

In Excel, choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). Run macros only when you trust their source.