Insert multiple columns using Excel VBA

This video will outline an approach via VBA, to insert multiple columns at once into an Excel worksheet. This insert multiple columns macro will give us the flexibility, to choose numerous columns to insert simultaneously.

 

Excel AGGREGATEStrategize FM – VBA insert multi columns.xlsb

 

Opening up Visual Basic for Applications

Begin with a custom header to notate the macro. Give a short, but concise macro name. In this case, we will call our macro “insertMultiCols“. Remember to disable the three Excel applications of Calculation, EnableEditing and ScreenUpdating. It is preferable to narrate each part of the code, which will appear in green. 

Next, it is time to declare variables in the code, which in this case is the column count variable – iCountCols.

 

Input feature of the macro

Now we are ready to write out the input feature of our macro. This will enable users to specify the number of columns to insert into the worksheet.

Take your time when typing out this part of the macro; this InputBox syntax must be 100% accurate. The underline is merely there to break up the length of the syntax, and place the remainder of the code on a second line – as is the case here.

Like with most VBA macros, it is wise to prevent erroneous inputs such as negative numbers. This piece of code will trigger the macro to end; if a negative number is entered.

The final element of the macro will trigger the insertion of multiple columns. Remember this code is quite different to the syntax used to insert multiple rows in a worksheet. We can now re-enable the three Excel applications, which were earlier disabled, whilst the macro was running.

 

Test driving the Insert Multiple Columns macro

The macro is now ready to test drive. As we can see, it is referencing column D by its column index number, which is not as intuitive as by its column letter. Thus, we will make a small adjustment to the code to facilitate this preference.

 

Fine-tuning the Insert Multiple Columns macro

We need to replace ActiveCell.Column, by adding a couple of declared string variables colRef and colRef2. Next we must Set these declared variables of colRef and colRef2colRef will be formatted to remove the absolute row number reference from the ActiveCell’s address – $1. 

Then colRef2 will use the combined function Right and Len to remove the $ from the absolute cell reference of the column reference. Now we can replace ActiveCell.Column with colRef2.

insert multiple columns

We can save the macro and workbook, and run the macro again, and see how it is referencing the ActiveCell’s column letter – in this case column D. We will demonstrate the macro again in column G, but this time we will insert 5 additional columns.

Therefore thanks to VBA, we have the ability to insert multiple columns at once.