All right. I've got two examples that are going to

sort of put together everything you've learned so far in this module.

In particular, If-Then statements combined with For loops. We got an example here.

Create a VBA sub that's going to delete all the negative 999s

and negative 998s in a column of data.

So let me explain the utility of this.

Here is a massive spreadsheet I was able to download off the NOAA website,

where we have the year, so 2002.

We have the month and the date.

And for each of these days,

we have a max of a high temperature for that day, a low temperature.

This is the precipitation.

This is the amount of snow,

and this is the snow that's on the ground on that day.

But, unfortunately, sometimes instruments aren't working.

And in that case, they usually output something like

a 9999 or a negative 9999 or a negative 999,

or here, they're also using negative 998,

which means that that data is not available,

that the instrument was not working.

Now, if you wanted to do something like you want to plot this data,

or you wanted to take the average, then obviously,

these very large negative numbers are going to affect the averages,

and it's going to screw up your plots and everything.

Rather than going through,

I think there's something like 48,000 rows here.

Rather than going through each one and searching manually for these,

you can create a subroutine that's going to go through and replace

those negative 998s or negative 999s with just a blank.

So that's what we want to do is we want to just

make a subroutine that's going to go through a column,

in this case, and it's going to delete those and just leave them empty.

As always, let's make a flowchart.

We start. We count the number of rows.

We're going to set up a For loop.

We're going to iterate through all the cells.

We're going to for each cell, and, again,

I'm denote Selection.Cells (i,1) as just cell (i,1) to save space.

If that cell value is equal to negative 999 or negative 998,

we're going to replace that cell with just empty quotations.

If it's false, then we're not going to do anything.

And we're going to loop back and increment i by 1.

When i exceed to the number of rows,

were done, and that's it.

There's nothing to output because what we're doing here

is we're making modifications to the individual cells.

We're not actually outputting anything at the end.

So we just end.

I've named my sub, Delete999.

We dim the two variables we're working with.

We just have i and the number of rows, which is nr.

We determine the number of rows using Selection.Rows.Count.

We set up our single For loop,

where we're doing a fixed iteration from the first row to the number of rows.

We set up our one way If-Then,

if Selection.Cells( i,1) equals negative 998,

or Selection.Cells.( i, j) equals negative 999,

then we're just going to say the new value in that selection is just empty, just blank.

So this is it. Now we can run this on our selection.

We start with a selection.

I'm going to just run through this using F8.

We count the number of rows. We have 14.

The first one is a 6.7.

So we keep going and going.

The first negative 999 is when i equals 5.

So you notice when i equals 5.

We go into the true arm of that one-way If-Then,

and we're going to replace it with just a blank.

We can keep going and going,

and the next one,

that was a negative 998 was encountered when i equals 9.

We keep going, and we finish.

So that's a example of where we

might want to implement what we've learned already in this course.

We have a selection going on inside of a fixed For loop.

I wanted to also point out there's another way to do this.

Instead of using selection,

we have to start with a selection because we're counting the number of rows up here,

but instead of using Selection.Cells( i,1) we can use the active cell.

So the active cell now is D1.

It's the first one by default.

If active cell equals negative 998 or active cell

equals negative 999, then ActiveCell.Clear.

Now we have to offset.

We have to change the activecell after each iteration,

after the one way If-Then statement.

We can do that using ActiveCell Offset (1, 0).Select.

So let's go through.

Make sure you have the selection here. We go through.

We count the number of rows.

Right now, the active cell is D1,

that's not equal to a negative number,

neither is the second.

And you notice here at the end of each iteration,

we offset the active cell by one row and zero column. So we move to the next.

And we check that,

and we check the next one.

Now, this one because it's a negative 999 ActiveCell.Clear.

And we keep going and going.

We have another one here. We just clear it, and we keep going.

So that's another way that you can delete

the contents that satisfy a particular constraint.