Get the Value at the Last Used Row and Column in Excel VBA
Question details
Retrieve the value at the intersection of the last used row and last used column and write it to a destination cell.

- 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.
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.
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.
In Excel, open the Developer tab and select Visual Basic, then open the module containing your macro.
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.
After calculating the row and column numbers, add: ws.Range("B28").Value = Destws_TCA_corp.Cells(lastRow_TCA_corp, lastCol_TCA).Value
Run the macro from the VBA editor and check cell B28 on the destination worksheet.

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.

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.




