Thanks for your help. I got it to work after moving wk.Cells.Select to
the other macro like this:

Sub DeleteBlanksAllSheets()
Dim wk As Worksheet
For Each wk In ActiveWorkbook.Sheets
wk.Select
wk.Cells.Select
Call DeleteBlankRows(wk)
Next wk
End Sub

Sub DeleteBlankRows(ByRef wk As Worksheet)
Dim i As Long

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub










wrote:
I get run time error 1004
select method of range class failed

for this line: wks.Cells.Select

I also tried just Cell.Select, but it would only work for the active
sheet in that case.


John wrote:
Dear Famous,

You just need to pass the worksheet object in your call statement:

Sub DeleteBlanksAllSheets()
Dim wk As Worksheet
For Each wk In ActiveWorkbook.Sheets
Call DeleteBlankRows(wk)
Next wk
End Sub

Sub DeleteBlankRows(ByRef wks As Worksheet)
Dim i As Long
wks.Cells.Select
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

Best regards

John

wrote in message
ups.com...
I have about 150 Worksheets in one Workbook. In all these sheets, I
need to delete the blank rows. I've already have a macro for that
purpose. Now I'm trying to figure out another macro for executing the
DeleteBlankRows macro on all the 150 worksheets. The code I have so far
only works on the active worksheet. I would really appreciate any help.
-------------------------------------------------
Sub DeleteBlankRows()

Cells.Select

Dim i As Long

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False

For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

-----------------------------------------
this is what I have so far for the other Macro:

Sub DeleteBlanksAllSheets()

Dim wk As Worksheet
For Each wk In ActiveWorkbook.Sheets

Call DeleteBlankRows

Next wk

End Sub