issues about deleting zero rows

[pmchris]

Hi,

I need vba code to delete those entire rows with column F equal to zero. First I want to delete those rows that have zero in column F in "Section 1" (Rows 5 to Row 21), and then get the total in row "Section 1 Sub-total" after deleting. And then do the same thing for section 2 and section 3. I've developed code for deleting, but it works fine until row 11. I use debug.print to show the results I get for each run. Here are my results. The range has total 17 rows, so it gets 17 0, 16 0, 15, 0.....When it runs to row 15 (row 11 in my defined range), I get the result 11 175. This is the result based on entire sheet, not my defined range. Can anyone help on this? I've attached my file.

17
0
16
0
15
0
14
0
13
0
12
0
11
175
10
165

Please Login or Register  to view this content.

[Trebor76]

Hi pmchris,

This line...

Rng.Rows.Count

...returns 17 so the code works from Row 17 not 21. You need use variables to determine the first and last row of the Rng variable and then loop backwards through those like so:

Please Login or Register  to view this content.

Or you just use this so you don't have to keep setting the Rng variable:

Please Login or Register  to view this content.

Regards,

Robert

[jolivanes]

In "Section 2" you have no data. Should "Section 2" be totally deleted from A25 to A56?
(Column F has no value in the row that has "Section 2" in Column A)

In see that in the meantime Robert has supplied you with an answer.

[pmchris]

Hi Robert,

Much appreciate for your explanations and 2 codes. Both codes work perfectly. The first code is straightforward and I can understand. For the 2nd code, what Range("F" & i) does? and how can I interpret If Len(ws.Range("F" & i)) > 0 And ws.Range("F" & i) = 0 Then or what is the logical behind this sentence?

[Trebor76]

what Range("F" & i) does?

It is checking each row i.e. i in Col. F

how can I interpret If Len(ws.Range("F" & i)) > 0 And ws.Range("F" & i) = 0 Then or what is the logical behind this sentence?

If there is something in Col. F for Row i and that value equals zero then delete the row. I came up with the logic from your posted example.

Hope that helps,

Robert

[pmchris]

Got it. Thanks again, Robert.

[Trebor76]

You're welcome. Thanks for marking the thread as solved and the rep