Hi there, this video will outline one way to delete rows based on a cell value in a spreadsheet
Here we have a file that contains trade data of fictional public companies,
which are listed down the page of our worksheet.
However,
let’s say we only want to present those securities, which have realised a change in market value –
as witnessed in column I.
Sure we can filter out zeroes based on column I,
but this might be a menial task that I need to do daily
and with a number of worksheets.
Therefore, we will explore a simple VBA macro, which will automate this process
and could save you a lot of time –
especially over a working week or year.
We need to insert a module firstly.
Give your macro a concise, yet self-explanatory name.
I am naming this –
deleteZeroRows.
In most cases, it’s wise to disable the three key Excel features of
Calculation,
EnableEvents
and ScreenUpdating.
Next, it is a good habit to declare the variables for the macro,
as this will improve the code’s efficiency and help to avert run-time errors.
The Visual Basic data type “long”,
as in long integer is applied to “deleteRow”,
because it will mitigate against integer numbers that are too large to fit in the Integer data type.
In other words, if we are wanting to apply this macro on a large data set,
the long data type will work error-free.
Now we will set our objects.
In the case of the worksheet object, ws, you need to be aware this macro will run on any worksheet.
If you want to restrict it to a certain worksheet, then you would want to explicitly state that i.e.
use Sheet1 or Sheet2 instead of ActiveSheet.
We can now write out the loop,
which will run through our data range
and rows containing zeroes in column I.
Although adding comments may seem a waste of time, they can be worthwhile
if we ever need to troubleshoot or review the macro in the future.
We will employ the For loop.
Further, we want this macro to be able to dynamically run across data sets of different ranges.
Hence the use of Rows.Count and the End(xlUp).Row syntax.
Remember, whenever you use the Range.End Property,
and End(xlUp),
the macro will run and start at the top of the range, and loop down the range of data –
which in this case is by each row.
Next, we need to clarify on what row the macro will start from;
which in this case will be from row 5 –
because we clearly want to preserve the information in rows 1 to 4.
Hence, we use the integer 5 in our code;
as this will ensure the macro starts on row 5.
As this description states,
we need to identify any cells in column I, which are zero
and instruct the loop to delete the entire row.
We can use a simple IF statement.
To delete the entire row, you must state this in your macro
Now we need to instruct the macro, after we have completed the IF statement, to move to the next cell
in the range.
Otherwise the loop of our data range won’t work.
This is simply the insertion of the Next syntax,
which is mandatory for a For loop.
Our loop is complete
and we can re-enable those Excel features,
which were disabled at the top of our code.
Let’s save our macro
and try the macro on our data set.
As mentioned,
we want this macro to delete any row,
which contains a zero in the column I range.
Let’s insert a form control or macro button at the top of the sheet, and assign our newly created macro –
deleteZeroRows.
We’ll quickly name our macro button,
which will be self-evident to other users of our financial model
Once we have saved it,
let’s run the macro and identify how
any zero cells in column I will trigger the deletion of that entire row from our worksheet.