Iain Lewis
Board Regular
- Joined
- Apr 15, 2002
- Messages
- 217
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.
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.