Hi, works for me I'm afraid.

Check the worksheet object is being passed through correctly. Step through
the code one line at a time (F8 key) and look at the Locals window
(View/Locals Window) to check that the wks object isn't empty.

My guess is it's just a typo. Did you cut and paste the code of modify what
you had?

Hope that helps

Best regards

John

wrote in message
ps.com...
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