Why Do You Need to Insert Anyway?

There are instances where we might forget to add data while creating a Word document or Excel spreadsheet. When we realize our mistake later, we can go in and add it using insert rows/columns, insert pages/shapes/images, etc., and by using the context menu of Microsoft Excel or Microsoft Word. Also, some data might need to be appended to the document on a conditional basis in a particular order, which again might result in needing to insert data.

Let’s look at the different types of data insertion one-by-one.

Insert Rows or Columns in Microsoft Excel

Rows or columns can be inserted into existing spreadsheets either manually or through VBA.

The Context Menu for Insert

Select a row 🡪 insert menu 🡪 insert one blank row above the selected row.

In this image, the inserted row would be row 3 (the row immediately above it):

Select a column 🡪 right-click 🡪 insert menu 🡪 insert a blank column to the left of the selected column.

In this image, the inserted column would be column B (the column immediately before it):

When we select a cell and then choose the “insert” option from the context menu, we can find a small dialog box with four options as shown in the image below:

The option we choose determines whether:

1. Only a cell will be inserted, pushing existing cells to bottom/right or
2. An entire row/column needs to be inserted.

The table below explains further what the options mean:

S. no	Option	Description
1	Shift cells right	Moves all the cells to the right of the selected cell to the right by one position as a new cell gets created in the place of the selected cell.
2	Shift cells down	Moves all the cells at the bottom of the selected cell to the bottom by one position as a new cell gets created in the place of the selected cell.
3	Entire row	Inserts a new row in the place of the selected cell by pushing all the rows below + current row down.
4	Entire column	Inserts a new column in the place of the selected cell by pushing all the columns on the right + current column to the right.

But what if your task is enormous? For example, what if we need to insert around 100 rows at different positions based on different criteria? That is when we need to rely on the help of VBA code.

Insert Rows and Columns Using VBA Code

Using VBA Range or selection we can insert rows or columns to an Excel spreadsheet or even the tables in a Word document.

Insert Function in Excel VBA to Insert Rows/Columns

Here is the syntax for the insert function in VBA:

Syntax

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Where 

1. The Shift parameter indicates the direction in which the selected and cells below or to the right have to be moved. The value would be xlDown for row insertions and xlRight for column insertions,
2. The CopyOrigin parameter has two possible values for xlFormatFromLeftOrAbove. 
   1. 0  is a default value which copies the formatting from the cells on the row above.
   2. 1 is a specific value if you do not want any formatting to be copied. 

Example of Inserting a Row Using VBA

This program selects the fourth row of an active spreadsheet and inserts a new row above it using the insert function.

Sub insertions()

'select row number 4
rows("4:4").Select

'insert a row above row number 4
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

End Sub

Example of Inserting a Column Using VBA

This program selects the fourth row of an active spreadsheet and inserts a new row above it using the Insert function.

Sub insertions()

'select column C
Columns("C:C").Select

'insert a row above row number 4
Selection.Insert Shift:=xlRight, CopyOrigin:=xlFormatFromLeftOrAbove

End Sub 

Output After Running Both the Examples Above

In this output, we can see that the 4^th row is a new/blank row that is inserted. Similarly, Col C is the new blank column inserted.

Insert Text in Word Using VBA

The Selection.InsertAfter and Selection.InsertBefore functions can be used to insert text in the open Word document using VBA.

Selection.InsertAfter

This code snippet inserts the text in the parameter in the open/associated Word document after the selected range of text or content. If nothing is selected, it is inserted after the cursor.

Sub selectioninsert_demo()
Selection.InsertAfter ("I'm in a dreamland.")
End Sub

Selection.InsertBefore

This code snippet inserts the text in the parameter in the open/associated word document before the selected range of text or content. If nothing is selected, it is inserted before the cursor.

Sub selectioninsert_demo()
Selection.InsertBefore ("I'm well educated.")
End Sub

Run both the procedures above, placing the cursor at different positions/selecting different text on an existing document. The output can help you understand how it works.

Inserting a Page Break

Selection.InsertBreak is the statement/function used to insert a page break in an existing document.

Here is the document in which we are going to insert a page break.

We have to place the cursor in between two lines and run the subprocedure below:

Sub selectioninsert_demo()

' code to insert a page break
Selection.InsertBreak

End Sub

The output is:

Insertions in a Table in Microsoft Word

Insertions like cells, rows, or columns can be performed on a table object inserted in a Word file.

Insert Cells to a Table

Selection.InsertCells without a parameter can insert the same number of cells (rows and columns) at the bottom of the selected table in the document.

Sub selectioninsert_demo()

' select the first table in the document ( specified in the index as 1)
ActiveDocument.Tables(1).Select
' insert same no of cells as in the selected table. This does not add another table. Just increments the no of cells
Selection.InsertCells
End Sub

Before running the code:

After running the code:

Insert Rows to a Table in Microsoft Word

Selection.InsertRows (< number of rows >) can insert the specified number of rows on top of the table, pushing down the existing content.

Sub selectioninsert_demo()
' select the table
ActiveDocument.Tables(1).Select
Selection.InsertRows (3)

End Sub

The code above will insert three rows at the top of the selected table.

InsertRowsAbove and InsertRowsBelow

Functions like InsertRowsAbove and InsertRowsBelow can explicitly be used to insert the rows above or below the table respectively.

Insert Columns to the Table in Microsoft Word

Selection.Insertcolumns can insert the existing number of columns to the left of the table pushing the existing content to the right. It does not support any parameters.

Sub selectioninsert_demo()
' select the table
ActiveDocument.Tables(1).Select
Selection.InsertColumns
End Sub

The code above will insert three columns to the left of the selected table.

Insert Columns to the Right of the Table

In the above example we noticed that the new columns get inserted to the left of the table by default. When the function “InsertColumnRight” is used, the columns get inserted to the right of the column.

Sub selectioninsert_demo()
' select the table
ActiveDocument.Tables(1).Select
Selection.InsertColumnsRight
End Sub

Here are the newly inserted columns to the right of the table when the above program is run:

Insertion of Date and Time

The code below helps in inserting the current date and time in the place of the cursor or selected text.

Selection.InsertDateTime

Please run the code above to see the output visually, as the current date keeps changing every day. 😊

Inserting a Formula

Formulas can be inserted in the selected area using the InsertFormula function.

Syntax: 

Selection.Insertformula ([<formula>] ,[<Number Format>])

Where formula can be any Excel formula and the number format parameter represents the format in which are expecting the output value to be.

Eg:

Selection.InsertFormula ("=sum(C1:C10)") 

Insert a New Page

The code below can help in inserting a new page using VBA in Microsoft Word.

Sub selectioninsert_demo()

' insert a new page
Selection.InsertNewPage

End Sub

Insert a Paragraph

The InsertParagraph function can help in inserting a new paragraph in the place of the selected text or the cursor.

Sub selectioninsert_demo()

' insert a new paragraph
With Selection
.InsertParagraph
End With
End Sub

Similarly, InsertParagraphAfter and InsertParagraphBefore functions can be used to insert the paragraphs before/after the selections respectively.

InsertFile, InsertXML, InsertSymbol, and InsertStyleSeparator are the other insertions that can be  achieved using VBA codes.

Conclusion

Insertion of rows/cells/columns are mostly useful with Microsoft Excel and the table objects of Microsoft Word. There are many other insertions discussed in this article that can be of much use in Microsoft Word.

These can be used in VBA macro of a Word document when the insertion is recursive based on the need.