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

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

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

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

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

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