Rows are the horizontal layers of data — whether empty or not — in any table. You can think of a worksheet in MS Excel as a table. Likewise in Word, tables can be inserted and used. Rows are essential part in any table used in either of these host applications that use VBA. When we need to insert blank rows above or below any existing row of data (or a blank row, for that matter),  we can use the insert row feature available in the context menu. This same feature is also offered by VBA, and I’ll demonstrate several methods below where it is implemented.

Insert a row into a table in a Word document

Using indices, we first have to refer to the table into which rows need to be inserted. Then, we can use the index (row index, that is) once again to refer to the row above or below which another new row needs to be inserted.

For example, if we need to insert a row above the 4^th row of the 3^rd table in a Word document, we can use the code below.

Tables.Item(3).Rows(4).Select
Selection.InsertRowsAbove(1)

If the row needs to be inserted below another row, we can use Selection.InsertRowsBelow(1) instead of Selection.InsertRowsAbove(1) . The number within parentheses indicates the number of rows to be inserted.

To learn more about this, check out the article below:

VBA, Word Table Insert/Remove Rows/Columns

Insert a row in a worksheet in MS Excel

Insert row manually

When we want to insert a row manually, we can simply select it, right-click and select the “insert” menu item from the context menu. This action will insert a new row above the selected row. You can insert multiple rows at a time by first highlighting multiple rows. You’ll then be promoted to insert that many rows when you view the context menu.

There is no option to insert rows below the selected row manually.

Insert row using VBA

There are many ways to do this using VBA. It can either be using simple code to just get the work done for the moment, you can develop a perfect add-in that can be used by anyone, at anytime as required. Here are some possible ways in which we can insert rows into a worksheet using VBA code.

Method 1

Open the worksheet in which rows need to be inserted. Open the VBA module, type the code below and run it . A row will get inserted above the row index mentioned in parentheses.

Sub row_insertion_demo()
Rows(2).Insert
End Sub 

Sub insert_demo()
Range("2:2").Insert
End Sub

This works on the open worksheet because no worksheet is specifically selected.

Method 2

The insert method used in the above example (method 1) actually has two optional parameters. It can be used for both the insertion of rows and of columns.

Syntax:

Range.Insert( <shift direction> , < Copy Origin> )

Where

<Shift direction> can be either

• xlShiftDown  or -4121 ( to shift rows of existing data down)

or

• xlShiftRight or -4161 ( to shift columns of existing data to the right)

And

<Copy Origin> can be either

• xlFormatFromLeftOrAbove or 0 ( to copy the format of new row from the existing row above it or the existing column to the left of it)

or

• xlFormatFromRightOrBelow or 1 ( to copy the format of new row from the existing row below it or the existing column to the right of it)

Here is an example that uses these optional parameters to shift the existing rows down and copies the data format from above for the newly inserted row.

Sub row_insertion_demo()
range("4:4").Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

Note:

Here is a piece of code that can help selecting the required worksheet and keep it active. This line of code should be inserted above the “insert specific code” for it to work.

Sheets("Wonders").Select

Method 3

Inserting row(s) using the Worksheet.Rows method. Since we are using a Worksheet object in the code, there is no need to keep the specified worksheet open for macro to run.

Sub insert_demo1()
Worksheets("Wonders").Rows(2).Insert
End Sub

(or)

To insert multiple rows:

Sub insert_demo1()
Worksheets("Wonders").Rows("2:5").EntireRow.Insert
End Sub

Method 4

This example uses Worksheet.Range method. This also does not require the worksheet to be active or open.

Sub insert_demo1()
Worksheets("Wonders").range("2:2").Insert
End Sub

(or)

Sub insert_demo1()
' to insert multiple rows
Worksheets("Wonders").range("2:5").EntireRow.Insert
End Sub

Method 5

This method again uses a worksheet and references the cells.

Sub insert_demo1()
Worksheets("Wonders").Cells(2, 5).EntireRow.Insert
End Sub 

Method 6

Insert row a using the currently “Active cell” ( or)  select a cell and do the same thing.

Sub insert_demo1()
Cells(3,2).Select
Application.ActiveCell.EntireRow.Insert
End Sub

Sample programs

Insert rows if the second column is blank

The program assumes that if data is available in the first column and the second column is blank, it is a heading and there should be a blank row immediately after it. So a blank row is inserted.

Sub row_insertion_demo()

' Get an input on the no of rows to be considered
Rowscount = InputBox(" Enter the number of rows to be checked for ")

    ' loop to iterate through rows
    For i = 1 To Rowscount
    
        ' check if the second column is blank and first column has data ( heading)
        If Sheets("Sheet2").Cells(i, 2).Value = "" And Sheets("Sheet2").Cells(i, 1).Value <> "" Then
            ' Insert a row immediately after that ( + 1 is used since rows will be shifted down )
            Worksheets("Sheet2").range(i + 1 & ":" & i + 1).Insert
            ' Changing to the next row in interation since the same old row should not be referenced again.
            i = i + 1
        End If
    
    Next
End Sub

Insert a few rows

In the same above program let us assume that 2 starters and 3 desserts are newly added to the food fest. So, rows need to be added using this program. All the blank rows in the sheet can be removed before running the code below.

Sub row_insertion_demo()

' Get an input on the no of rows to be considered
Rowscount = InputBox(" Enter the number of rows to be checked for ")

    ' loop to iterate through rows
    For i = 1 To Rowscount
    
        ' check if the second column is blank and first column has data ( heading )
        If Sheets("Sheet2").Cells(i, 1).Value = "Starters" Then
            ' Insert 2 rows immediately after that ( + 1 is used since rows will be shifted down )
            Worksheets("Sheet2").range(i + 1 & ":" & i + 2).Insert
            ' Changing to the next row in interation since the same old row should not be referenced again.
            i = i + 1
        ElseIf Sheets("Sheet2").Cells(i, 1).Value = "Desserts" Then
            ' Insert 3 rows immediately after that ( + 1 is used since rows will be shifted down )
            Worksheets("Sheet2").range(i + 1 & ":" & i + 3).Insert
            ' Changing to the next row in iteration since the same old row should not be referenced again.
            i = i + 1
        End If
    
    Next
End Sub

The required rows are now inserted successfully.

Conclusion

Though it may be easy to insert rows manually, when we want to insert several rows at different locations (rows numbers) or on certain criteria as in the above sample programs, VBA comes to the rescue. Though VBA is quite useful for this purpose, any action done by running a VBA macro code cannot be reversed.

Though insertion of rows doesn’t affect the existing data, it can be tedious to identify the unnecessary rows that were inserted and then delete them at different locations manually. So, your logic needs to be well thought over before running it on any important document because of these risks.

For example , the second example has ( i +1) indicated for insertion and incremental iterations which a beginner might not think of until he faces an issue with the working of the code. So, it is advisable to run the code on sample or unimportant documents rather then trying on the actual or important ones.