View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
[email protected] famehunter@gmail.com is offline
external usenet poster
 
Posts: 7
Default Run Macro on All Worksheets in a workbook

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