VBA & Macro Problems

Use Excel VBA to Fill a LEN Formula Down a Column

Camila MilosovichCamila Milosovich Nov 18, 2026 868 views

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.

Use Excel VBA to Fill a LEN Formula Down a Column
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In desktop Excel, open the workbook, select the worksheet you want to update, and press Alt+F11 to open the VBA editor.

2
Insert the macro

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

3
Run the macro

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.

4
Optionally keep values only

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

Fill column N with LEN formulas using a VBA macro
Formula results: LEN returns 0 for blank cells. Formulas update when source cells change; converted values do not.
Free Microsoft Office alternative

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.

Calculate text lengths with the LEN functionOpen and edit Microsoft Excel .xlsx filesUse a familiar interface for an easier transition from ExcelWork with a free, lightweight office suite
QA img-9

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.