VBA & Macro Problems

Excel VBA: Select a Range to the Cell Above the Downward Endpoint

Natalie TaylorNatalie Taylor Nov 22, 2026 868 views

Question details

The user needs the correct VBA syntax to select a range ending one cell above the downward endpoint, or to work with that range directly.

Select a Range to the Cell Above the Downward Endpoint in Excel VBA
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
Writing a macro that starts at the active cell and finds an endpoint by moving down, then up one row.
Observed behavior
The expression passes three arguments to Range, which does not accept that syntax.
Before you start

In Excel, identify the starting cell and check for blank cells below it. End(xlDown) follows the same navigation behavior as Ctrl+Down Arrow, so blanks or an isolated starting cell can change the endpoint.

Solution 1Recommended

Select the Range Using Two Cell References

Use this when you want to select every cell from the active cell through the cell above the downward endpoint.

1
Open the VBA editor

In Excel, open Developer > Visual Basic and locate the macro you want to edit.

2
Replace the invalid expression

Use two endpoints in Range: Range(ActiveCell, ActiveCell.End(xlDown).Offset(-1, 0)).Select

3
Test the selection

Return to the worksheet, click the desired starting cell, and run the macro from Developer > Macros. Check that the highlighted range has the intended endpoint.

Select the Range Using Two Cell References
Selecting only the endpoint: To select just the cell above the downward endpoint instead of the entire range, use ActiveCell.End(xlDown).Offset(-1, 0).Select.
Free Microsoft Office alternative

Work with Spreadsheet Ranges in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and compatibility with common Excel formats, making it easy to move everyday spreadsheet work. Excel-specific VBA behavior and macro support may differ by WPS version.

Select and edit spreadsheet ranges in a familiar interfaceOpen and edit common Microsoft Excel XLSX and XLS filesUse a free, lightweight office suite for everyday spreadsheet tasks
QA img-9

Frequently Asked Questions

Why does the original Range expression fail?

Range accepts one cell reference or two endpoints, not three separate arguments. Put the Offset call on the second endpoint: Range(ActiveCell, ActiveCell.End(xlDown).Offset(-1, 0)).

How do I select only the cell above the downward endpoint?

Use ActiveCell.End(xlDown).Offset(-1, 0).Select in the macro. This selects one cell rather than the range from the starting cell.

What happens if there are blank cells below the starting cell?

End(xlDown) navigates like Ctrl+Down Arrow. Depending on the starting cell and surrounding data, it can stop at a boundary or jump to the bottom of the worksheet. Check the result before using it as an endpoint.

Do I have to select the range before changing it?

No. Assign it to a variable with Set target = Range(ActiveCell, ActiveCell.End(xlDown).Offset(-1, 0)), then use the variable directly for the desired operation.