Automatically number rows in Excel

Use a simple Visual Basic for Applications (VBA) script to automate the process of numbering new rows in Microsoft Excel.

According to Microsoft, there is no way to automatically number new rows in Excel. Microsoft recommends adding sequential numbers to rows of data by dragging the fill handle to fill a column with a series of numbers or using the ROW function.

A simpler way is to use a macro to automate the process.

The following script, called NewRow_NewID will find the last row of data in a table, create a new row below it, then ‘Fill Series’ by +1 in Column A of that new row. To make the process easier, the shortcut Ctrl + r has been employed.

Sub NewRow_NewID()
'
' NewRow_NewID Macro
' Create a new row with a new row number
'
' Keyboard Shortcut: Ctrl+r

'Selects the row after the last row that contains data

Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Rows(lr).Activate

'Takes the contents of Column A above and Fills Series +1

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(-1, 0).Range("A1:A2").Select
ActiveCell.Activate
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Trend:=False
        
End Sub

Note that in order for this to work, you will need to manually create your first row and add a row number to the cell in Column A.

Have Your Say

Δ