Run Macro on all WS in active workbook..

[jeremiah]

I don't use Excel programming very much so an unfamiliar with out to run the
below listed macro. I have 3 sheets to run this on and would like to
automate this instead of running it manually. How do I make this work for
each sheet, rather than just the active sheet?


Dim rg As Range, rgBlank As Range
'-------- CHANGE HERE -----------
Set rg = ActiveSheet.Range("A:A")
'--------------------------------

'get blank cells from rg
On Error Resume Next
Set rgBlank = rg.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rgBlank Is Nothing Then 'no blank cell
MsgBox "No Blank cells found"
Else 'else delete entire rows
rgBlank.EntireRow.Delete
End If
End Sub

 

[Norman Jones]

Hi Jeremiah,

Try something like:

'=========>>
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range

Set WB = Workbooks("myBook.xls") '<<==== CHANGE

For Each SH In WB.Worksheets
On Error Resume Next
Set Rng = SH.Columns("A:A").SpecialCells(xlBlanks)
On Error GoTo 0

If Not Rng Is Nothing Then
Rng.EntireRow.Delete
End If
Next SH

End Sub
'<<=========

 

[Dave Peterson]

Just a warning...

Under certain circumstances, the code could cause an error. If there is a sheet
that has blanks in column A and that sheet that follows it doesn't have any
blanks in column A (in the usedrange), then rng won't be nothing on that second
sheet. It won't be a range since it was already deleted, too.

Just setting rng to nothing can avoid this error:

Public Sub Tester2()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range

Set WB = Workbooks("myBook.xls") '<<==== CHANGE

For Each SH In WB.Worksheets
Set Rng = nothing '<-- added.
On Error Resume Next
Set Rng = SH.Columns("A:A").SpecialCells(xlBlanks)
On Error GoTo 0

If Not Rng Is Nothing Then
Rng.EntireRow.Delete
End If
Next SH

End Sub

 

[Norman Jones]

Hi Dave,

You are correct; it is necessary to reset
the range variable at the start of each cycle

Thank you!

 

[Ron de Bruin]

And also check out this page to be sure <g>
http://www.rondebruin.nl/specialcells.htm

 

[Norman Jones]

Hi Ron,

And also check out this page to be sure <g>
http://www.rondebruin.nl/specialcells.htm

That page I know very well!

 

[jeremiah]

As always, you guys always come through for me. Works great!