This video will outline an approach, via Visual Basic for Applications or VBA, to insert multiple columns at once into an Excel worksheet.
Currently Excel only permits users to insert one column at a time.
But what if we had the flexibility to choose numerous columns, to insert simultaneously?
By opening up Visual Basic Editor and then inserting a module, which will house our macro;
we will realise this goal.
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
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.
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 letter.
Thus, we will make a small adjustment
to the code to facilitate this preference.
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 ColRef2.
colRef will be formatted to remove the absolute row number reference from the ActiveCell’s address -
Then colRef2 will use the combined function Right and Len, to remove the $ sign from the absolute cell reference
of the column reference colRef.
Now we can replace ActiveCell.Column with colRef2.
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.