Deleting rows containing zero within specified range

Hi NewbieVBA and welcome to the forum,
Open your sheet and click on Record Macro.Click on OK when it gives the Record Macro Name (remember this name).
Click on the entire row 15 (click on the "15" left of A15 and delete it). Stop recording.

Click on Visual Basic icon to see what has been recorded. You need to click on Module 1 in the Projects area if you can't find it. Here is what I recorded when I did the above.

Please Login or Register  to view this content.

1. Always leave the Option Explicit as the first line of every module as it will help find easy spelling bugs before you try to run your code.
2. Sub Macro1() is the name of this Macro and you can run it from the Macros Icon
3. Every Sub needs and "End Sub" so it knows where to stop

This leaves only 2 lines of code you need to study.

You wanted to do a Test to see if it was a zero or one and do all this from row 15 to 130 in youir question. Lets see if we can figure that out.

We want the 15 from the above code to go from 15 to 130 so lets creata a variable that will cover these numbers. Do that with a DIM statement. DIM says to create a variable and also tells what type of variable it is. I like to use varialbe names that mean something to me so I'm going to use RowCtr (Row Counter to go from 15 to 130). Some guys like to use i or j or k which work fine but are a little less obvious on what they are really doing.

"Dim RowCtr as Double" is the line that creates this variable for VBA.

Now how dow we make this variable work in the "Rows("15:15").Select?????
How about Rows("RowCtr:RowCtr").Select - which doesn't work..... Now what???
Jump to the help file or internet to see how to use Rows in VBA. It says I can select a whole row by just doing a "Rows(15)" without needing two of them or the colon. I like that better. Click down to the Immediate Window (View it if not shown) and type "Rows(17).Select" and press enter. If you get an error message then VBA doesn't like what you typed. If it follows your instructions then your syntax is correct... YEH!!!

Somehow we have to do all those rows - 15 to 130 and I like to do a For Next Loop. The code would look like:

Please Login or Register  to view this content.

Now for the if what is in the A Column =0 or not part of the code.
In the Immediate Window do a "Debug.Print Range("A17")" and press enter. VBA likes this statement and returns a 1 or zero. I can use this type of statement in the VBA to get to...

Please Login or Register  to view this content.

Note that I indented each section of the Loop and If statments so it is easy to understand where it ends.

After running this code I see there are some problems. THIS ALWAYS HAPPENS!!! Don't let programmers tell you their code works perfectly the first time!!!

The problem after using BreakPoints and Stepping through the code. see http://www.cpearson.com/excel/Debug.htm
Is that if there are two zeros in adjacent rows it misses the second one. The reason is that when the code did a Shift:=xlUp the RowCtr then skipped over that row. To fix that we can move the RowCtr back one with a "RowCtr = RowCtr -1 " statement. So this might work now..

Please Login or Register  to view this content.

Programmers who write clean code don't like the For Next Loop and they don't like the .Select type of statements as they slow things down. My intent from the above is to take you from the Macro Recorder to show you how to move to the next level.

We need to have some good programmers write the above code in a more efficient method. You will see then count backwards from 130 to 15 using a "Step" parameter in the For Next loop so the Shift:=xlUp will work without the need to subtact 1 from the RowCtr. You will see them combine the .Select and Selection into a single line like:

Please Login or Register  to view this content.

You might even see them leave the Shift out completely.

BTW - I believe you need to learn and almost memorize the following.

Please Login or Register  to view this content.

I like to use "Cells" instead of "Range" when writing code. I think I learned to always put the Row first and the Column second (kind of like (x,y) when graphing stuff in school).

I hope this gives you a start in writing your own code.