VBA & Macro Problems

How to Use VBA to Copy the Last Used Cell in a Row

Elise Williams Oct 22, 2026 868 views

Question details

The user needs a VBA script to automatically locate the last populated cell within a specific row and copy its text or numeric value to a different target cell.

How to Use VBA to Copy the Last Used Cell in a Row

Product: Excel / WPS Spreadsheets
Device & OS: not provided
Scenario: Automating data retrieval to extract the most recently added entry at the end of a dynamically updating row.
Observed behavior: The target cell successfully reflects the value of the furthest right non-empty cell in the designated row.

Before you start

Ensure you have the Developer tab enabled in your spreadsheet application and that your workbook is saved as a Macro-Enabled Workbook (.xlsm) to allow VBA scripts to run.

Solution 1Recommended

Use the End(xlToLeft) Method in VBA

The most reliable approach to find the last used cell in a row is by instructing VBA to navigate from the extreme right edge of the worksheet back to the left.

By utilizing the 'Columns.Count' property, the script starts at the very last column of the spreadsheet. It then uses 'End(xlToLeft)' to simulate pressing 'Ctrl + Left Arrow', effectively stopping at the first cell that contains data. This guarantees accurate retrieval even if there are blank cells earlier in the row.

Open the VBA Editor

Press the Alt + F11 shortcut keys on your keyboard to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

In the top menu, click 'Insert' and select 'Module' to create a blank script window for your code.

Paste the Copy Code

Type the following command into the module: Cells(1, 1).Value = Cells(5, Columns.Count).End(xlToLeft).Value. This specific code copies the value from row 5 into cell A1 (Row 1, Column 1).

Run the Macro

Press F5 or click the green 'Run' triangle button on the toolbar. Check your worksheet to see the value copied to the target cell.

Adjusting Cell and Row References: In the 'Cells(RowIndex, ColumnIndex)' syntax, adjust 'Cells(5, ...)' to target a different source row, and 'Cells(1, 1)' to target a different destination cell.

Advanced Automation Made Easy

Use WPS Spreadsheets for Seamless Macro Execution

WPS Office provides robust and built-in support for VBA macros, making it incredibly easy to automate tasks like finding and copying the last used cells in your datasets.

1. Download and Install WPS Office: Download WPS Office from the official website and open your spreadsheet file.
2. Access the Developer Tab: Navigate to the Developer tab on the top ribbon to access the built-in VBA and Macro tools.
3. Run Your VBA Code: Click 'VBA Editor', paste the 'End(xlToLeft)' script, and run it to instantly automate your data copying.

Highly compatible with Microsoft Excel VBA macro files (.xlsm and .xlsb)Run VBA macros to automatically locate and copy last used cells without lagFree and intuitive Developer tools interfaceLightweight application that uses minimal system resources

Frequently Asked Questions

Will this VBA code copy the underlying formula or just the displayed value?

The provided code uses the '.Value' property, which means it will only copy the resulting text or number. If you need to copy the actual formula instead, change '.Value' on both sides of the equation to '.Formula'.

How do I find the last used cell in a column instead of a row?

To find the last used cell vertically in a column (e.g., column A), you must navigate upwards from the bottom. Use the syntax: Cells(Rows.Count, 1).End(xlUp).Value.

Why is the VBA code returning an empty value?

This usually happens if the entire target row is completely blank. The 'End(xlToLeft)' method will slide all the way to column 1. If column 1 is also blank, it has no data to copy and will return an empty string.

Excel & Data Problems