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.