Autofill Excel Formulas From Row 2 Without Overwriting Headers
Question details
Fill Excel formulas through the last data row without replacing headers in row 1.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A VBA macro needs to determine the last data row and populate formula columns starting in row 2.
- Observed behavior
- Ranges that begin in row 1 overwrite the worksheet headers.
Check that row 1 contains headers and that column H reaches the last row you want to process. Confirm the worksheet name and destination columns before changing the macro.
Assign formulas from row 2 to the last data row
Use column H to find the final row, then write each formula to a range that starts below the headers.
The example uses a worksheet named Data and fills columns L, M, and P. Change the sheet name, columns, and formulas to match your workbook. Existing values or formulas in those destination ranges will be replaced.
In Excel, open the workbook and select Developer > Visual Basic. In the VBA editor, select Insert > Module.
Paste this example into the module: Sub FillFormulas() Dim w As Worksheet Dim m As Long Set w = ThisWorkbook.Worksheets("Data") m = w.Cells(w.Rows.Count, "H").End(xlUp).Row If m <= 1 Then Exit Sub w.Range("L2:L" & m).Formula = "=D2" w.Range("M2:M" & m).Formula = "=MONTH(B2)&""/""&DAY(B2)&""/""&YEAR(B2)" w.Range("P2:P" & m).Formula = "=IF(AND(O2>=Q$1,O2<Q$2),1,IF(AND(O2>=Q$2,O2<Q$3),2,3))" End Sub
Return to Excel, select Developer > Macros, choose FillFormulas, and select Run. Check that row 1 is unchanged and that the formulas extend through the last data row.

Work with spreadsheet formulas in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for working with spreadsheets. Its Excel format compatibility makes it easy to move everyday workbook tasks to WPS, although it does not fix Excel VBA macros.

Frequently Asked Questions
How does the macro find the last data row?
The expression w.Cells(w.Rows.Count, "H").End(xlUp).Row finds the last nonempty cell in column H. Choose another column if H does not reliably reach the end of your data.
What happens if there are no data rows?
The line If m <= 1 Then Exit Sub stops the macro when column H has no data below row 1, so no formula range is written.
Will the formulas adjust for each row?
Yes. When the formula is assigned to the range beginning at L2, relative references such as D2 adjust for each row. References such as Q$1 keep row 1 fixed.
How do I keep the macro in my workbook?
In Excel, select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). Only enable macros in workbooks you trust.
Does the P formula return text or numbers?
The example returns the numbers 1, 2, and 3. If you need text instead, use quoted text values in the Excel formula and double the quotation marks inside the VBA string.




