Ok, the next item. But first, this word from the Teacher: It's probably obvious to you by now, but you have to understand that writing macros isn't magic; each statement in the program does some particular thing (kind of like a series of steps in an algebraic solution), and by following each instruction—each of your instructions—Excel accomplishes what you wanted...or doesn't, if your instructions were faulty. So if I tell you something and it isn't clear to you why or how, then ask.

Ok, you have a program that deletes some columns, then runs from rows 2 to 264 and colors certain rows. Now let's show it how to spot the list row and do the coloring only up to that point.

There's more than one way to do this. (Programmers always say that, and it's always true.) We can have the program simulate a <Ctrl-Ins>, which jumps to the last used cell in the worksheet. My own favorite is to do <End>,<Down>, which works great if you're in a column that has data all the way to the end, with no empty cells I mean. If there are empty cells in the column you want to use, you can go to the bottom of the sheet, or at least so far down that you're confident that it's past the end of the worksheet (say row 99999), and then do <End>,<Up>, which will jump to the first row —the first row, that is, that it encounters in the upward direction — that isn't empty. Many programmers would find it natural to just tell the program to keep going from row 2 onward, and stop when it gets to a row that's empty. And I'm sure there are other ways; there always are.

Feel free to decide for yourself what makes sense to you, because programming styles are as varied as programmers; but I'll start with the one that I like. For this we're going to start with row 1 and any column that has data unbroken from top to bottom. I'll use column K ("View Filter") in the worksheet I'm looking at. The sequence, then, will be this:

1) Start with K1.
2) Hit <End>,<Down>. Excel finds the next empty cell in the "Down" direction, and puts the cursor just above it.
3) What row is that?

In VBA it looks like this:The first statement finds K1 and points to it using the variable name "c1", which I made up arbitrarily.

The second statement says to start with c1 and from there do an <End>,<Down>. Only instead of hitting two keystrokes, in VBA End is a sort of sub-program (called a "method") that works on cells and xlDown is a keyword to VBA/Excel that tells it the direction. What End hands back as a result is another cell, the one at the bottom of data. That cell, K23 in the worksheet I have here, is now labeled c2 in my program.

The third statement asks for the Row "property" of c2 (which of course is 23), and puts that in a variable I named "rz", which in my programming style means the last row. (You will inevitably work out names for things that suit you, not me; that's as it should be.)

So the result of those three statements is that rz is set to 23, and then your next statement is...instead of "...to 264", you see.

Only we're not going to do three extra statements. You can combine them in various ways:I'd probably go with 5 or 7, myself, but it doesn't matter; they all do the same. What matters is that it makes sense to you, so that if you look at it six months from now (by which time you will have forgotten the details) it'll be clear to you what you were doing and why. This, by the way, is why programmers say you should write as many comments as you think you need to help you remember:Let me know how that goes (whichever form you use) and we'll talk about the next step.