Use VBA to Add Row-Number Columns Beside Excel Data
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.

- 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.
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.
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.
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.
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
Return to the target worksheet, choose Developer > Macros, select AddRowNumberColumns, and click Run. The inserted columns shift existing columns to the right.
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).

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.

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.




