Apply to all sheets not working

[hgufrin]

Can anyone tell me why the "apply to all worksheets" portion of this code is not working? I appreciate your time.

Sub Delete_0activityaccount()
'
'
Dim mywSheet As Excel.Worksheet
For Each mywSheet In ActiveWorkbook.Worksheets


lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow To 8 Step -1
If Cells(i, 4).Value = 0 And Cells(i, 5).Value = 0 And Cells(i, 6).Value = 0 And Cells(i, 7).Value = 0 And Cells(i, 8).Value = 0 _
And Cells(i, 9).Value = 0 And Cells(i, 10).Value = 0 And Cells(i, 11).Value = 0 And Cells(i, 12).Value = 0 _
And Cells(i, 13).Value = 0 And Cells(i, 14).Value = 0 And Cells(i, 15).Value = 0 And Cells(i, 16).Value = 0 _
And Cells(i, 17).Value = 0 And Cells(i, 18).Value = 0 Then
Rows(i).Delete
Else
End If
Next i

Next mywSheet

End Sub

 

[texasalynn]

try this
For Each mywSheet In Worksheets

 

[schielrn]

I don't see anywhere you are declaring the sheet with the cells portion of the code?

You are probably meaning to do something like:

Sub Delete_0activityaccount()
'
'
Dim mywSheet As Excel.Worksheet
For Each mywSheet In ActiveWorkbook.Worksheets
with mywsheet

lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow To 8 Step -1
If .Cells(i, 4).Value = 0 And .Cells(i, 5).Value = 0 And .Cells(i, 6).Value = 0 And .Cells(i, 7).Value = 0 And .Cells(i, 8).Value = 0 _
And .Cells(i, 9).Value = 0 And .Cells(i, 10).Value = 0 And .Cells(i, 11).Value = 0 And .Cells(i, 12).Value = 0 _
And .Cells(i, 13).Value = 0 And .Cells(i, 14).Value = 0 And .Cells(i, 15).Value = 0 And .Cells(i, 16).Value = 0 _
And .Cells(i, 17).Value = 0 And .Cells(i, 18).Value = 0 Then
.Rows(i).Delete
Else
End If
Next i
end with
Next mywSheet

End Sub

But since you didn't specifically say waht this code was doing or anything I will try to be a mind reader and hopefully it works out for you or you can modify it to your needs?

Hope that helps.

 

[hgufrin]

schielrn,
I was expecting you to be a mind reader...

On all sheets in my workbook... I want to delete any row that has 0's in all columns D-R.

I just cant get your code down below to work across all worksheets...

 

[schielrn]

Is it only numbers in columns D:R or can there also be text?

 

[hgufrin]

only numbers. if there are $0's in there (across all columns D-R) I will delete the entire row..

 

[schielrn]

Try this:

Sub Delete_0activityaccount()
'
'
Dim mywSheet As Excel.Worksheet
For Each mywSheet In ActiveWorkbook.Worksheets
with mywsheet

lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow To 8 Step -1
if worksheetfunction.sum(.range("D"& i, "R" & i)) = 0 then .Range("D" & i).entirerow.Delete
Next i
end with
Next mywSheet

End Sub

Hope that helps.

 

[hgufrin]

downright irritating. cant seem to do anything on my own...

i appreciate this. it works great!

 

[schielrn]

You were close and not sure why my original code didn't work? All you were missing was qualifying the sheets becasue your code was only looking at the active sheet even when looping through all the sheets.

You learn from this so it is a great resource.

 

[hgufrin]

schielrn,

Hate to bug you again. Come to find out after sifting through hundreds of pages of data... rows are being deleted that shouldn't be. Rows that contain text... Rows that contain numbers formatted as text that are spreadsheet headers.

can you modify my original code to work? each cell in columns D-R must contain the number ZERO for it to be deleted...

I do thank you.