Run Macro on all WS in active workbook..
 

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

Run Macro on all WS in active workbook..
 

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
'<<=========



---
Regards.
Norman


"jeremiah" wrote in message
...
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

Run Macro on all WS in active workbook..
 

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 wrote:

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
'<<=========

---
Regards.
Norman

"jeremiah" wrote in message
...
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

--

Dave Peterson

Run Macro on all WS in active workbook..
 

Hi Dave,

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

Thank you!


---
Regards.
Norman


"Dave Peterson" wrote in message
...
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 wrote:

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
'<<=========

---
Regards.
Norman

Run Macro on all WS in active workbook..
 

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Norman Jones" wrote in message ...
Hi Dave,

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

Thank you!


---
Regards.
Norman


"Dave Peterson" wrote in message
...
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 wrote:

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
'<<=========

---
Regards.
Norman

Run Macro on all WS in active workbook..
 

Hi Ron,

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


That page I know very well! :-)



---
Regards.
Norman

Run Macro on all WS in active workbook..
 

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


"Norman Jones" wrote:

Hi Ron,

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


That page I know very well! :-)



---
Regards.
Norman