Use Excel VBA to Fill a LEN Formula Down a Column
Question details
The user needs an Excel VBA macro that calculates the length of values in column K and fills the results in column N through the last used row.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet has source values in column K, and the user wants to populate column N automatically.
- Observed behavior
- The desired result is a LEN formula or its calculated value in column N for each row through the last populated row in column K.
The macro assumes row 1 contains headers and data starts in row 2. Check that column N can be overwritten, then save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you want to keep the macro.
Fill column N with LEN formulas using a VBA macro
Use this macro to find the last populated row in column K and fill the corresponding cells in column N.
The macro uses the active worksheet. It fills every row from row 2 through the last populated row in column K, including any blank cells between them.
In desktop Excel, open the workbook, select the worksheet you want to update, and press Alt+F11 to open the VBA editor.
In the VBA editor, select Insert > Module and paste the following code: Sub FillLenFormula() Dim lastRow As Long With ActiveSheet lastRow = .Cells(.Rows.Count, "K").End(xlUp).Row If lastRow < 2 Then MsgBox "No data found in column K from row 2 onward." Exit Sub End If .Range("N2:N" & lastRow).FormulaR1C1 = "=LEN(RC[-3])" End With End Sub
Return to the intended worksheet, open Developer > Macros, select FillLenFormula, and click Run. Column N will contain formulas referencing the corresponding cells in column K.
To replace the formulas with their results, add these two lines immediately after the FormulaR1C1 line in the macro, then run it: .Range("N2:N" & lastRow).Calculate .Range("N2:N" & lastRow).Value = .Range("N2:N" & lastRow).Value

Calculate text lengths in WPS Spreadsheet
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and use LEN formulas in WPS Spreadsheet; Excel VBA macro support may vary by WPS version and installation.

Frequently Asked Questions
How does the macro find the last row?
It uses Cells(Rows.Count, "K").End(xlUp).Row on the active worksheet to find the last populated row in column K.
How can I start the formulas on a different row?
In the VBA editor, replace both occurrences of row 2 in the range and the last-row check with your starting row. For example, use "N5:N" and check whether lastRow is less than 5.
Will the formulas update when column K changes?
Yes, when Excel recalculates the worksheet. If you use the optional lines to replace formulas with values, those results will not update automatically.
Why do some cells in column N show zero?
The macro fills all rows through the last populated row in column K. LEN returns 0 for a blank source cell, so gaps in column K produce zeros.
Where can I run the macro in Excel?
Select the intended worksheet in desktop Excel, then choose Developer > Macros, select FillLenFormula, and click Run. Save the workbook as .xlsm to retain the macro.




