Excel VBA Macro to Fill Static Values in Columns D, E, and F from Column C
Question details
Create a VBA macro that detects the used data range in column C and fills columns D, E, and F with specified static values only for populated rows.
- Product
- Microsoft Excel VBA macro for a CSV worksheet
- Device & OS
- not provided
- Scenario
- The user needs to add state, name, and city values to a variable-length CSV data set that begins on row 3.
- Observed behavior
- The macro must dynamically find the final data row and avoid adding values to rows where the corresponding cell in column C is blank.
A CSV file cannot store VBA code, so keep the macro in an .xlsm workbook, the Personal Macro Workbook, or another macro-enabled file. Confirm that the source data starts in row 3 and that column C is the column used to identify valid records.
Loop through column C and fill only nonblank rows
Use this method when column C can contain blank rows anywhere in the data and those rows must remain unchanged.
The macro finds the last nonblank cell in column C, then checks each row from row 3 onward before writing values to columns D, E, and F.
In Excel, press Alt+F11, then select Insert > Module to add a standard code module.
Paste this code into the module: Sub FillStaticFields() Dim lastRow As Long Dim r As Long With ActiveSheet lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row For r = 3 To lastRow If Len(.Cells(r, "C").Value) > 0 Then .Cells(r, "D").Value = "KY" .Cells(r, "E").Value = "OWEN" .Cells(r, "F").Value = "RUSSELLVILLE" End If Next r End With End Sub
Return to the worksheet, press Alt+F8, select FillStaticFields, and choose Run. The macro writes values only on rows whose column C cell is not blank.
Fill the three columns in bulk when column C data is contiguous
Use this faster method only when every row from C3 through the final used row contains data and there are no blank rows in between.
Fill Static CSV Fields with a WPS Spreadsheet Macro
WPS Spreadsheet can open CSV data, identify populated records in column C, and use a VBA-compatible macro workflow to write fixed values to columns D, E, and F. Save the macro in a macro-enabled workbook and export the completed data as CSV when needed.
- 1. Open the data in WPS Spreadsheet: Open the CSV file in WPS Office, then verify that records begin on row 3 and column C identifies populated rows.
- 2. Create a macro module: Open the Developer tools or VBA editor in WPS Spreadsheet, then choose Insert > Module.
- 3. Add the row-checking code: Paste a macro that finds the last used cell in column C and uses an If check before writing KY, OWEN, and RUSSELLVILLE to columns D:F.
- 4. Run the macro on the active sheet: Choose the macro from the Macro dialog and select Run to populate only rows with a value in column C.
- 5. Export the completed records: Use Menu > Save As and select CSV if the finished file must remain in CSV format; keep the macro separately in an .xlsm workbook.
Frequently Asked Questions
Why does using End(xlDown) stop my macro before the last CSV record?
Starting with Range("C3").End(xlDown) stops at the first blank cell below C3. Use Cells(Rows.Count, "C").End(xlUp).Row to locate the last populated cell in column C instead.
Can a CSV file contain the VBA macro?
No. CSV files store text values only and cannot retain VBA code. Store the macro in an .xlsm workbook, a separate macro workbook, or the Personal Macro Workbook, then save or export the processed data as CSV.
How can I avoid filling D, E, and F when column C is blank?
Loop through the rows and test the column C cell before assigning values, such as If Len(Cells(r, "C").Value) > 0 Then. Put the assignments to columns D, E, and F inside that condition.
What happens if there is no data below row 3 in column C?
The bulk method should include If lastRow >= 3 Then before creating the destination ranges. The loop method will not process any rows when the calculated last row is above row 3.
Can I replace KY, OWEN, and RUSSELLVILLE with other fixed values?
Yes. Edit the text on the three assignment lines: .Cells(r, "D").Value, .Cells(r, "E").Value, and .Cells(r, "F").Value. You can also change the target column letters if your layout differs.
