Only works on One Worksheet, When Looping thru All WS
 

I am trying to get the operations listed inside the first
For loop to work on each Worksheet as it goes thru each
one. I know it steps thru them, I've used a msgbox to
show each ones name. But it will only perform the
requested operations on ONE worksheet. What am I doing
wrong? Thx for your help - Dean



Sub DeleteAllNoneDataRowsx()
'This will only work on One Tab at a time
'
Dim wks As Worksheet
Dim r As Integer
For Each wks In Worksheets
LastRow = ActiveSheet.UsedRange.Row - 1 +
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.WorksheetFunction.CountA
(Rows(r)) = 0 _
Then Rows(r).Delete Else
If ActiveSheet.Cells(r, 15).Value
= "Total" _
Then Rows(r).Delete Else
If ActiveSheet.Cells(r, 15).Value
= "Value" _
Then Rows(r).Delete Else
If ActiveSheet.Cells(r, 15).Value = "" _
Then Rows(r).Delete
Next r
Next wks
End Sub

Only works on One Worksheet, When Looping thru All WS
 

Hi Dean

You use activesheet and this is always the same sheet in your code
One way isto add a select line in the loop

For Each wks In Worksheets
wks.Select

Or change you code so that it will use the reference to the sheet in the loop
This is better and faster

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Dean" wrote in message ...
I am trying to get the operations listed inside the first
For loop to work on each Worksheet as it goes thru each
one. I know it steps thru them, I've used a msgbox to
show each ones name. But it will only perform the
requested operations on ONE worksheet. What am I doing
wrong? Thx for your help - Dean



Sub DeleteAllNoneDataRowsx()
'This will only work on One Tab at a time
'
Dim wks As Worksheet
Dim r As Integer
For Each wks In Worksheets
LastRow = ActiveSheet.UsedRange.Row - 1 +
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.WorksheetFunction.CountA
(Rows(r)) = 0 _
Then Rows(r).Delete Else
If ActiveSheet.Cells(r, 15).Value
= "Total" _
Then Rows(r).Delete Else
If ActiveSheet.Cells(r, 15).Value
= "Value" _
Then Rows(r).Delete Else
If ActiveSheet.Cells(r, 15).Value = "" _
Then Rows(r).Delete
Next r
Next wks
End Sub

Only works on One Worksheet, When Looping thru All WS
 

Ron,
You have fixed it in one step. I had tried earlier
using
Worksheets (wks).Select, but that didn't do it. Using
your
wks.Select it worked the first time. Thanks very much.
Dean


-----Original Message-----
Hi Dean

You use activesheet and this is always the same sheet in
your code
One way isto add a select line in the loop

For Each wks In Worksheets
wks.Select

Or change you code so that it will use the reference to
the sheet in the loop
This is better and faster

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Dean" wrote in
message ...
I am trying to get the operations listed inside the first
For loop to work on each Worksheet as it goes thru each
one. I know it steps thru them, I've used a msgbox to
show each ones name. But it will only perform the
requested operations on ONE worksheet. What am I doing
wrong? Thx for your help - Dean



Sub DeleteAllNoneDataRowsx()
'This will only work on One Tab at a time
'
Dim wks As Worksheet
Dim r As Integer
For Each wks In Worksheets
LastRow = ActiveSheet.UsedRange.Row - 1 +
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.WorksheetFunction.CountA
(Rows(r)) = 0 _
Then Rows(r).Delete Else
If ActiveSheet.Cells(r, 15).Value
= "Total" _
Then Rows(r).Delete Else
If ActiveSheet.Cells(r, 15).Value
= "Value" _
Then Rows(r).Delete Else
If ActiveSheet.Cells(r, 15).Value = "" _
Then Rows(r).Delete
Next r
Next wks
End Sub


.