The following code will delete all rows in all worksheets where the
cells A:D on each row are empty. If one or more cells in A:D on a row
have a value, that row is not deleted. Change the "A:D" to whatever
columns you want to test.

Sub AAA()
Dim WS As Worksheet
Dim R As Range
Dim N As Long
Dim LastRow As Long
Const CHECK_RANGE As String = "A:D" '<<<<<< CHANGE

For Each WS In ThisWorkbook.Worksheets
With WS
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For N = LastRow To 1 Step -1
If Application.CountA(.Range(CHECK_RANGE).Rows(N)) = 0
Then
.Rows(N).Delete
End If
Next N
End With
Next WS
End Sub

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Tue, 15 Dec 2009 13:52:01 -0800, Roger on Excel
wrote:

I utilize forms for running an excel spreadsheet.

I have a macro which deletes empty rows (of a specified range). I have it so
the macro works over several sheets.

The macro works fine, however it needs to activate and perform its actions
in each sheet in turn to select the rows and delete them.

I now activate the macro from a userform, and was wondering if there was a
way to execute the code such that the macro doesnt require each sheet to be
activated (ie visible) to work. It is quite distracting having the sheets
scrolling through in the background.

I tried to activate the code with the sheets hidden but the code doesnt work
when I do this.

i have attached the code I use for information. It seems somewhat ungainly
and I wondered if there was a better way to do this without having to have
the sheets visible. I eventually would like all the sheets hidden so the
user doesnt need to access them.


Public Sub DeleteRows()

Dim Rng As Range, Rng1 As Range

Set Rng = Range("A81:E140")

On Error Resume Next
Set Rng1 = Intersect(Rng, Columns("B:B").SpecialCells(xlBlanks))
On Error GoTo 0

If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete
End Sub

Can anyone help or advise me?

Thasnkyou, Roger