vba to delete rows

[Tripper]

I have a worksheet "Report" that is populated with revenue and expense
information by referencing a pivot table. When I select a new pivot
table page (department), the proper accounts and amounts appear on
"Report".

Different departments have different numbers of revenue and expense
rows, so "Report" has lots of rows of each type to accomodate any
department. I have a macro that copies the data to another sheet
called "Report (2)" so the original "Report" will stay intact. On
"Report (2)" I am trying to remove unnecessary blank rows.

I am a VBA newbie, so I copied some code from another thread and
modified it. It works, but it only removes six empty rows. If I run
it again, it removes six more empty rows. I can't figure out why it
only removes six empty rows at a time. Here's the code for the
revenue area:

'Remove blank revenue rows
Sub DeleteRows()
Set currentCell = Worksheets("Report (2)").Range("M12")
For Each cell In Range("M12:M28")
Set nextCell = currentCell.Offset(1, 0)
If Value(currentCell.Value) = 0 Then
currentCell.EntireRow.Delete
End If
Set currentCell = nextCell
Next
End Sub

Column M contains a value of zero only if certain cells in the row are
blank. If so, the entire row should be removed.

The revenue area on "Report (2)" is from row 12 to row 28.

Could anyone help a novice?

Thanks,
Dan

 

[Tom Ogilvy]

when you loop forward in this fashion as a row is deleted, the remaining rows
slide down one row, so the next row is skipped (it is never examined). So
you need to loop from highest to lowest. (come up from the bottom)

sub Delete rows
Dim i as Long
with Worksheets("Report (2)")
for i = 28 to 12 step -1
if isnumeric(.cells(i,"M")) then
if Int(.cells(i,"M")) = 0 then
.rows(i).Delete
end if
end if
Next
End With
End sub

 

[Charles Chickering]

When deleting rows you should always work backwards. This is what is happening:
Say Row 12 and 13 are blank, you test row 12, it is blank so you delete it,
now row 13 becomes row 12 and contains a blank, but you tell excel to go to
the next row, 13 and evaluate it, thus the original row 13 never got
evaluated/deleted. Try this macro:
Sub DeleteRows()
Dim cnt As Long
For cnt = 28 to 12 Step - 1
If Range("M" & cnt) = 0 Then Rows(cnt).Delete
Next
End Sub

 

[Don Guillett]

The trick is to work from the bottom up

Sub DeleteRows()
for i=28 to 12 step -1
if cells(i,"M")=0 then rows(i).delete
next i
End Sub

 

[Tripper]

Thanks so much for the quick responses. Now it makes perfect sense to
me.

Dan