The above image will give you idea about what I am trying to explain. Let us assume, I have a list of data (or a simple table of data) on 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 a 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.
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 won’t 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.
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.
Thanks for reading.