Excel VBA: Select a Range to the Cell Above the Downward Endpoint
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.

- 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.
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.
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.
In Excel, open Developer > Visual Basic and locate the macro you want to edit.
Use two endpoints in Range: Range(ActiveCell, ActiveCell.End(xlDown).Offset(-1, 0)).Select
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.

Work with the Range Without Selecting It
Use a range variable when the macro needs to process the cells rather than highlight them.
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.

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.




