How to Insert Rows in Excel Automatically

Thanks to this free vba code, you will insert an ActiveX Control which will add rows to your table automatically. Your worksheet gains some code and you will save a lot of time.

Pivot table data preparation

Consider the data. Go to ribbon. Click Developer > Insert and from ActiveX Controls choose a Command Button.

insert activeX forms

Draw a button like shown below.

command button

How to auto insert row?

Right-click the command button and choose Properties.

add rows excel properties

In the Caption field change the text to Add Row.

caption add row

When you double click the button, a form will appear like this below.

sheet1 code add rows excel

Put the code in like shown below. While keeping in mind the sheet name, range according to your data.


Private Sub CommandButton1_Click()

ActiveCell.EntireRow.Insert Shift:=xlDown

Selection.Borders.Weight = xlThin

End Sub


vba code

Click the Design mode to off design mode.

design mode off

Every time you click the Add Row button, a new row will be added in your data like shown below.

add row button works

Note: Of course, you can improve that VBA code. But to do this, you need to know a little bit of VBA in Excel. Just click VBA in the menu at the top of the page to learn VBA in Excel.

Further reading: 
Add a Combo Box to the Worksheet
Removing of unwanted rows
How to Sort Out Blank Rows?