How to Insert Rows and Columns in Excel VBA

In this blog post, you will learn about inserting rows and columns in Excel with the help of VBA.

Inserting Rows and Columns in Excel

Basically, when we insert rows and columns in Excel, we follow the below procedure.

Select the cell or row or column, before which we want to insert a new Row or a Column.

Press Right-click on the cell and select the “Insert” option from the right-click properties and then Excel asks whether to insert rows or columns.

If we want to insert rows then we select rows or if columns then we will select the column option.

But here in this blog post, we are going to learn about inserting rows and columns with the help of VBA.

How to Insert Columns in Excel By VBA

See in the below image, we have a dataset and we are missing one column here which we want to name as “North” and enter the value under this column that belongs to it.

See below:

Image -01

Here, you can see that we have data for the zones, East, West, and South and now we want to insert a new column for zone name "North". Which we will insert with the help of VBA.

So for inserting a new column we will use the following steps in VBA.

Go to the Developer tab in Excel and click “Visual Basic”, click on “Insert” and Click on “Module” for writing a VBA code.

As per the rules, Excel will always insert rows or columns before the current or active column or rows.

Here we want to insert a new column before “C” titled “South”.

 

Sub Insert_New_Columns() Range("C:C").Insert 'this code will insert a new column End Sub

Just execute the above code by pressing “F5” for a single time execution or press “F8” again and again for step by step execution.

See the result below:

See in the above image, a new column inserted, column “C” and pushed our dataset "South" just one column next, i.e. in column “D”.

Here, we inserted a single column but suppose we need to insert multiple columns here, then our VBA code for that will be as follows:

Sub Inset_New_Column() Range("C:E").Insert 'this will insert 3 (C,D,E) new columns End Sub

After executing the code, see the image below:

Three columns("C", "D"and "E")  have been inserted within the dataset.

How to Insert Rows Within Dataset by Excel VBA

Now we will insert the rows within the given dataset.

Suppose that we have a dataset where we need to insert single or multiple rows within the dataset, see the code below for single-row insert:

Sub Insert_New_Rows() Range("4:4").Insert 'this code will insert new row "4". End Sub

 

See the result after executing the above code:

This code is made for inserting multiple rows such as 3 or 5 within the dataset. 

See below the VBA code for the above-mentioned result:

Sub Insert_New_Rows() Range("4:6").Insert 'this code will insert new row "4 to 6". End Sub

See the result below after executing this VBA code.

So we learned here to insert rows and columns in Excel with the help of VBA.

I hope you find this tutorial helpful.

Please feel free to put your comments and suggestion.

Thanks

Narendra

How to use the MsgBox in Excel VBA

Easiest Way to Copy and Paste Data in Excel VBA

How to Change the Background Color in Excel VBA

What is Current Region Property in VBA

What is Current Region Property in VBA

How to Change Font Color in Excel VBA