How to Use VBA to Insert Rows in Microsoft Excel: 3 Best Steps
Automating an Excel sheet using Visual Basic programming language is popular among advanced Excel users, businesses, and developers. VBA-based actions, for example, VBA insert row will automatically add rows with a single click.
Furthermore, Excel VBA can insert rows according to specific logic, like inserting a row when the third column is blank, inserting rows after the currently active cell, and so on. Hence, continue reading to learn how to utilize VBA to insert rows in an Excel worksheet.
1. Use VBA to Insert Row: Single and Multiple
You can write simple VBA code to insert one or multiple rows after designated cell references using the Insert method and EntireRow property of the Excel VBA editor.
Here are the steps you can try along with the VBA codes:
- Open the target Excel workbook and access any Excel worksheet where you want to insert a row.
- Press Alt+F11 to bring up the Excel VBA editor.
- In the code editor, look for the Insert tab on the menu section and click on it.
- Now, click on Module to open an empty VBA code editing module.
- Copy-paste the below-mentioned VBA code to insert a single row. You can change the cell range if you wish to insert the row in a different location.
Sub vba_insert_row()
Range("A3").EntireRow.Insert
End Sub
Once you’re done typing the code, hit F5 to execute the VBA macro. A new row will show up in the cell reference you mentioned in the VBA code.
To insert multiple rows, change the cell reference from one cell address to a range of cells. For example, you need two rows between A3 and A6, enter A4:A5 as the cell reference. The VBA code shall look like this one after you insert the cell range:
Sub vba_insert_row()
Range("A4:A5").EntireRow.Insert
End Sub
2. Use VBA to Insert Row: New Copied Row
You can use the following small code to copy a row and paste it into another location.
Sub vba_insert_row() Rows(4).Copy Rows(6).Insert Shift:=xlShiftDown End Sub
If you run this code, Excel will copy the A4 row and paste it in between A5 and A7 row, which is the A6. You should note that VBA-based automated modifications can not be undone using the Ctrl+Z command. You need to manually delete the changes.
3. Use VBA to Insert Row: No Formatting
Let’s say you want to insert a row between two rows without copying formatting from the adjacent rows. You can do that easily by running this VBA insert row code:
Sub vba_insert_row() Rows(5).EntireRow.Insert Rows(5).ClearFormats End Sub
Upon running the aforementioned VBA script, a blank and formatting-free row will appear where you want it.
Final Words
You may find it easier to insert rows in small Excel files using the Insert functions or the right-click context menu. However, if you’re working on big data analytics or an Excel sheet of thousands of rows manual row insertion becomes a time-consuming process. Here, you can apply the above-mentioned VBA insert row codes to save time.
Are you looking for more fun things that Excel VBA can do for you? Here’s another one for you: spell check in Excel made easy with VBA.
