Hey there, this is a follow-up to a request we
received, on the back of an earlier video - "Deleting rows based on a cell value". 0:00:08.066,0:00:08.000
"Deleting rows based on a cell value".
One viewer wanted to know how you could
delete columns based on a cell value, such as
a blank or zero value as we have
in the following columns of a data set.
We need to compose a macro, which will
delete columns M, L, F and D, because these
columns contain some cells that are blank or zero.
Go into "Visual Basic for Applications" and insert a module.
We will call the macro, "deleteZeroCols"
Remember to disable these three Excel
properties before we write the macro.
Now we must declare all the object variables in the macro
Next, there are two objects which we want to state.
We will assume our data set, excluding the
headings, always start in cell B5 but is dynamic.
Hence, we want to identify the last row of data.
As well as the last column of data.
This will guarantee the macro can always
delete any columns in a data set, 0:05:27.033,0:05:27.000
which contain cells that are zero or blank.
Before we can run the macro, we need
to select our dynamic range of data.
Now that we know our dynamic range,
based on the selected data, we can write
the loop to identify cells that are blank or zero.
Hence, any blank or zero cells will
trigger the deletion of the cell's column.
The macro has finished looping through the selected range.
We will now move the cursor back to the active cell in B5.
The macro is complete.
We can now re-enable those three Excel properties.
Let's quickly review the macro,
before we attempt to run it on our data.
We will now run the macro to delete columns
containing any blank or zero cells.