VBA & Macro Problems

Get the Value at the Last Used Row and Column in Excel VBA

Jason MillerJason Miller Nov 18, 2026 868 views

Question details

Retrieve the value at the intersection of the last used row and last used column and write it to a destination cell.

Get the Value at the Last Used Row and Column in Excel VBA
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
A VBA macro has calculated the last used row and column on a source worksheet and needs to copy their intersection to cell B28 on another worksheet.
Observed behavior
The macro needs a single cell value rather than an entire range assigned to the destination cell.
Before you start

Identify the source and destination worksheets and confirm that your macro has calculated valid row and column numbers for the source sheet. Check that the destination cell B28 is the intended output location.

Solution 1Recommended

Copy the Intersection Cell with the VBA Cells Property

Use this approach when the last used row and column numbers are already available in your macro.

1
Open the VBA editor

In Excel, open the Developer tab and select Visual Basic, then open the module containing your macro.

2
Assign the worksheets

Ensure the worksheet variables point to the correct sheets. For example, use Set Destws_TCA_corp = ThisWorkbook.Worksheets("Source") and Set ws = ThisWorkbook.Worksheets("Destination"), replacing the sheet names with your own.

3
Assign the cell value

After calculating the row and column numbers, add: ws.Range("B28").Value = Destws_TCA_corp.Cells(lastRow_TCA_corp, lastCol_TCA).Value

4
Run and check the macro

Run the macro from the VBA editor and check cell B28 on the destination worksheet.

Copy the Intersection Cell with the VBA Cells Property
The intersection may be blank: The last used row and last used column can come from different cells, so their intersection is not necessarily the last nonblank cell.
Free Microsoft Office alternative

Work with Excel Spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open Excel files and continue working with your data in WPS Spreadsheets; check VBA support in your WPS edition before relying on an Excel macro.

Open and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interface for reviewing cell valuesMove existing spreadsheet files into WPS Office without starting overUse a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why use Cells instead of Range for the source value?

Cells(row, column) identifies one cell using numeric row and column values. Use its Value property to assign that cell's value to ws.Range("B28").Value.

What if B28 is blank after the macro runs?

Check the source worksheet and the calculated row and column numbers. Their intersection may be blank even when other cells in that row and column contain data.

How do I make sure the macro uses the correct worksheet?

Assign each worksheet variable explicitly, such as Set ws = ThisWorkbook.Worksheets("Destination"), using the actual sheet name. Qualify both Range and Cells with their respective worksheet variables.

Does this code calculate the last used row and column?

No. The assignment uses the existing lastRow_TCA_corp and lastCol_TCA variables. Calculate them for the source worksheet before running the assignment.