How to Dynamically Fill Excel Formulas Based on Another Sheet's Last Row
Question details
The user needs to write a VBA macro that detects the last row containing data in one worksheet and automatically fills formulas down to that exact row in a different worksheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating data processing across multiple worksheets where the number of rows in the source data fluctuates frequently.
- Observed behavior
- Formulas are currently hardcoded to a fixed row number, which leads to missing calculations when source data expands or unnecessary processing for blank rows.
Ensure that you have enabled the Developer tab in your spreadsheet software and backed up your workbook before writing or modifying VBA macros to prevent accidental data loss.
Use VBA Cells.Find to Detect Last Row and Apply Formulas
The most reliable method to dynamically fill formulas is to calculate the last populated row on the source sheet and append it to your destination range string.
Hardcoding row numbers in VBA can cause errors when your data grows. By dynamically finding the last row using the `Cells.Find` method, your macro will always adapt to the exact size of your source dataset. This ensures that formulas are only applied where needed.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
In the Project Explorer pane on the left, right-click your workbook name, select 'Insert', and click 'Module' to create a new script area.
Define a variable to store the last row. Use the Find method to locate the very last cell with data on your source sheet. For example: `LastRow = Worksheets("Source Data").Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row`
Construct the destination range dynamically by concatenating the column letter with the LastRow variable. For example: `Worksheets("Destination").Range("A2:A" & LastRow).Formula = "='Source Data'!A2*2"`
Press F5 or click the 'Run' button in the toolbar to execute the code. Check your destination sheet to verify the formulas have populated correctly to the exact last row.
Automate Your Workflows with WPS Spreadsheets
WPS Office offers robust support for VBA macros, allowing you to automate repetitive tasks like dynamically filling formulas across worksheets without missing a beat.
- 1. Download and Install: Download WPS Office and open your .xlsm file in WPS Spreadsheets.
- 2. Enable Developer Tools: Navigate to the Developer tab to access the built-in VBA Editor.
- 3. Run Macros: Paste your dynamic row detection code and run it seamlessly to automate your formula population.
Frequently Asked Questions
Why is my VBA code filling formulas past the actual data?
This usually happens if you use `UsedRange.Rows.Count` instead of `Cells.Find`. `UsedRange` remembers previously edited cells even if their contents were later deleted. Using `Cells.Find(What:="*")` is much more accurate for finding the last row that currently contains visible data.
How do I fill formulas in multiple columns dynamically with VBA?
You can easily expand your destination range reference. Instead of typing `Range("A2:A" & LastRow)`, modify it to `Range("A2:D" & LastRow)` to fill your formula array across columns A through D simultaneously.
Does this macro update automatically when new data is added?
No, standard VBA macros only run when triggered manually. To make it update automatically, you must call your macro from a `Worksheet_Change` event on the source sheet, so it triggers whenever the source data is modified.
Can I use End(xlUp) instead of Cells.Find?
Yes, using `Range("A" & Rows.Count).End(xlUp).Row` is a popular and fast alternative. However, it only checks one specific column (e.g., Column A). `Cells.Find` is safer if you want to find the absolute last row across the entire worksheet regardless of which column is the longest.
