VBA & Macro Problems

Autofill Excel Formulas From Row 2 Without Overwriting Headers

Muhammad TalhaMuhammad Talha Nov 18, 2026 868 views

Question details

Fill Excel formulas through the last data row without replacing headers in row 1.

Autofill Excel Formulas From Row 2 Without Overwriting Headers
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, open the workbook and select Developer > Visual Basic. In the VBA editor, select Insert > Module.

2
Add the macro

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

3
Run and check the formulas

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.

Assign formulas from row 2 to the last data row
Check the example formulas: The M formula returns a date-like text string from B. The P formula returns numbers 1, 2, or 3 using the value in O and thresholds in Q1:Q3. Adjust these formulas if your data or expected results differ.
Free Microsoft Office alternative

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.

Enter and fill spreadsheet formulas while keeping header rows intact.Open and work with Microsoft Excel XLSX and XLS files.Use a familiar spreadsheet interface for everyday editing.Move compatible workbook tasks to a lightweight office suite.
microsoft office alternative - wps office

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.