Add or Insert a new row in Excel on button click using VBA

I am sharing a simple VBA example here, which explains how easily you can insert or add a new row automatically in between existing rows in your Excel worksheet, at the click of a button.

The above image will give you an idea about what I am trying to explain.

Let us assume, I have a list of data (or a simple Excel table) in my Excel worksheet, with a unique id (a number series) for each row. As and when required, I want to insert a new row in between an existing row, anywhere in the list and at the same time alter the codes (in a sequence), automatically on button click.

Let’s do it.

Add two columns (or more) and few rows data to your Excel worksheet. Next, add an ActiveX Button control anywhere on the sheet (assuming sheet1). Save the Excel file in .xlsm format, (it’s the macro format).

Open the VBA editor to write the macro.

The VBA Code

We will write the macro on the button’s click event, because we want to insert the new rows automatically on button click.

Option Explicit
 
Private Sub CommandButton1_Click()
    addNewRow
End Sub
 
Sub addNewRow()
    
    ' Do not insert a row before the first row.
    Dim iTopRow As Integer
    iTopRow = 1
    
    If (ActiveCell.Row > iTopRow) Then

        ' Get the active row number.
        Dim rowNum As Integer
        rowNum = ActiveCell.Row
        
        Rows(rowNum).EntireRow.Insert       ' Insert a new row.
        
        ' Change the Codes (in first column).
        Cells(ActiveCell.Row, 1) = rowNum - 1       ' For the active cells.
        
        Dim iTotalRows As Integer   ' Get the total used range rows.
        iTotalRows = ActiveSheet.UsedRange.Rows.Count
        
        Dim iRows As Integer
        
        For iRows = rowNum + 1 To iTotalRows
            Cells(iRows, 1) = iRows - 1
        Next iRows
    End If
End Sub

Run the macro. To check if the code is working, set focus on a cell where you want to insert a new row, anywhere in your Excel worksheet and click the command button.

You can select the entire row and then click the button.

Simply set focus on a cell and click the button.

Insert a newline in Excel automatically

The code will automatically insert a new row before the selected row (the row you’ve set focus on) or the cell you’ve set focus.

Now explaining the procedure

The button’s click event calls a procedure named "addNewRow()". The variable "iTopRow" has a value 1, so the macro will not insert rows before the first row in your worksheet. The first row might have the headers. Therefore, don’t do anything there.

Next, I’ll get the "row number" of the selected row and insert a new row using the "EntireRow.Insert" property.

Rows(rowNum).EntireRow.Insert

Finally, I am updating the Codes (the number series) of the remaining rows, in the first column. Since every code must be unique.

That’s it. Using the above method, you can add or insert multiple rows with the click of a button.

← PreviousNext →