VBA & Macro Problems

Use VBA to Add Row-Number Columns Beside Excel Data

Kushani NimanthikaKushani Nimanthika Nov 18, 2026 868 views

Question details

The user wants a VBA macro that adds a row-number column beside each Excel data column and stops at an empty column.

Use VBA to Add Row-Number Columns Beside Excel Data
Product
Microsoft Excel
Device & OS
not provided
Scenario
Number worksheet rows beside multiple data columns automatically.
Observed behavior
The desired output is a row-number column next to each data column, with processing ending at the first empty column.
Before you start

Confirm where the data starts, whether row 1 contains headers, and whether the numbers should represent worksheet row numbers or a separate sequence. The example below assumes data starts in column A, row 1 contains headers, and an entirely empty column marks the end of the data.

Solution 1Recommended

Insert Row-Number Columns with an Excel VBA Macro

Use this example when your data columns start in column A and the first entirely empty column marks the stopping point.

The macro inserts a column to the right of each data column and fills it with worksheet row numbers from row 2 through the last used row of that data column. It also numbers gaps within that range.

A column containing a formula is not considered empty, even if the formula displays a blank. If your layout or numbering requirements differ, adapt the macro using a sample of the current and expected worksheet layouts.

1
Open the VBA editor

In desktop Excel, open the worksheet to process, then open the VBA editor from Developer > Visual Basic. If the Developer tab is hidden, enable it in Excel's ribbon settings.

2
Add a module

In the VBA editor, select your workbook and choose Insert > Module. Paste this code into the module: Sub AddRowNumberColumns() Dim ws As Worksheet Dim col As Long, lastRow As Long, r As Long Set ws = ActiveSheet col = 1 Do While Application.WorksheetFunction.CountA(ws.Columns(col)) > 0 lastRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row ws.Columns(col + 1).Insert ws.Cells(1, col + 1).Value = "Row number" For r = 2 To lastRow ws.Cells(r, col + 1).Value = r Next r col = col + 2 Loop End Sub

3
Run the macro

Return to the target worksheet, choose Developer > Macros, select AddRowNumberColumns, and click Run. The inserted columns shift existing columns to the right.

4
Check and save the result

Check the inserted columns and stopping point on the worksheet. To retain the macro, choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm).

Insert Row-Number Columns with an Excel VBA Macro
Adjust for a different layout: If data starts in another column, blank columns occur within the data, or you need a sequence starting at 1 instead of worksheet row numbers, the code needs to be changed.
Free Microsoft Office alternative

Work with Excel Data in WPS Spreadsheet

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Excel format compatibility for an easy transition. You can insert numbering columns manually; VBA availability and macro compatibility depend on your WPS version and configuration.

Insert columns beside data and use row-number formulas.Open and edit Microsoft Excel XLSX and XLS files.Navigate a familiar ribbon-style interface.Use a free, lightweight office suite.
microsoft office alternative - wps office

Frequently Asked Questions

What makes the macro stop at a column?

The macro checks each original data column with COUNTA and stops when the entire column contains no values or formulas. A formula that displays an empty string still counts as content.

Can I number the data starting at 1 instead of using worksheet row numbers?

Yes. In the VBA editor, change the assignment inside the loop from ws.Cells(r, col + 1).Value = r to ws.Cells(r, col + 1).Value = r - 1. With data starting on row 2, the first number will be 1.

Why does the macro number blank cells between data entries?

It fills every row from row 2 through the last used row of each data column. Add a condition inside the For loop if blank data cells should be left unnumbered.

Can I run this VBA macro in Excel for the web?

No. Open the workbook in desktop Excel to use the VBA editor and run the macro.

How do I keep the macro in my workbook?

In desktop Excel, choose File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). A regular XLSX file cannot retain VBA code.