*For Each Y in Range* not working properly

[Iain Lewis]

I have a sheet ( called Hours by Department ) which is summing the data from another sheet in the same workbook. It gives a summary of hours by department ( no great surprise there ) for one of my projects. But, as there are 43 departments & only 6 working on this project I want to delete the rows ( departments ) which are not working on the project. I am currently using the following code :

Sub aaa()
lastrow = Range("A65536").End(xlUp).Row
lastcol = Range("IV1").End(xlToLeft).Column
Dim Y As Range
For Each Y In Range("B2", Cells(lastrow, 2))
If WorksheetFunction.Sum(Range(Y.Offset(0, 1), Cells(Y.Row, lastcol))) = 0 Then
Y.EntireRow.Delete
End If
Next Y
End Sub

The sheet is set up as :

Col A - Dept. #
Col B - Dept. Name
Cols C to DQ - week #

There are 37 rows which have a sum of 0 across cols C to DQ. The first time the code is run it will delete 19 of the 37 rows, then if I run the code again it'll delete another 10 rows, then 5 rows etc until I've run the code 6 times & it's deleted all of the 37 rows with a 0 value in cols C to DQ.

Anyone got any ideas what I've done wrong ? I'm using XL 97 SR2 on NT 4.

 

[bergy]

I see the problem. When you delete a row, row 2 for example, the row below moves up. When you do the 'Next' statement you are now in row 3, which was row 4. You skip over the original row 3.

I would suggest a while loop where you could increment a row number if you don't delete, but not increment on a delete.

I do like the rest of your code. You guys are good!

 

[Iain Lewis]

Thanks bergy - I'd just worked it out.