Insert multiple column and rows in vba excel – debugvba.com

Objective

To add rows and columns in the worksheet.

Approach

Here we have used Insert method of the range object to add the rows and columns. We had taken the sample data which had 3 columns and 9 rows. In order to add columns, we have first selected the columns where we want to add the columns. Then we have used the insert method to add the columns.

Code to add columns

In the code below we have selected 2 columns and then we have applied the insert method on the selected range.

Sub AddColumn()

'Select the columns where you want to add new columns
Columns("A:B").Select

'Add new columns
Selection.Insert Shift:=xlShiftToRight, CopyOrigin:=xlFormatFromRightOrBelow
Range("A1").Select

End Sub

 

In the below image we can see how the data looks like before and after adding the column. We can see in the image that 2 columns have been added to left side (highlighted in green box). Hence before adding the columns we had 3 columns and after executing the VBA code we had 5 columns.

 

Code to add rows

In the code below we have selected rows 5 and 6, and then used the insert method to add the rows. So, before making any changes we had 9 rows in the sheet, while after executing the VBA code, we have 11 rows.

Sub AddRows()

'Select the columns where you want to add new columns
Rows("5:6").Select

'Add new columns
Selection.Insert Shift:=xlShiftToDown, CopyOrigin:=xlFormatFromRightOrBelow
Range("A1").Select

End Sub

 

In the image below, on left side we can see how the worksheet looked like before adding the row, while in the image on the right side we can see how worksheet looked like after adding the rows.

Note

1. We can also add cells using similar approach
2. We can change the format using CopyOrigin option, as shown in the code. If we are inserting the row, then we can copy the format of the row placed below or above. If we are inserting the column then we can copy the format of left or right column to newly added column.
3. We can get more details about it by the link given in reference section below.

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.range.insert

Post You may like

Check if a file exists or not in VBA Excel