Welcome. This video will demonstrate one way to simultaneously insert columns, and then fill across these new blank columns.
As we can see the imported financial data has skipped the data for 2015 and 2016.
This macro will firstly insert columns, as per our specification, and then fill across these inserted columns in our spreadsheet.
Before the macro runs, we want to disable the following Excel features.
and declare the count
Declare the column count variable – iCountCols
The last column variable – lastCol,
and the column reference variable – colRef
We then must set the colRef variable based on
the location of the active cell in our spreadsheet.
An input box is required to register the number of columns,
which the user wants to input into the spreadsheet.
It is always wise to add an error handling script,
as it will help to safeguard our macro from erroneous inputs
such as negative or zero values.
As we have notated here, this part of the macro
will insert columns
based on the input box value in the above.
Finding the last column in our spreadsheet
will help with the fill across action of the macro.
We have completed the code that will insert columns,
and then fill across the empty columns in our spreadsheet.
Hence, we can now re-enable our Excel properties
that were earlier disabled.
Let’s now insert these two missing columns,
and witness the macro filling across the missing data.