ExcelBanter - Removing blank lines in a workbook

Sue,

Try something like the following:

Sub surface()
Dim WS As Worksheet
Dim LastRow As Long
Dim X As Long
For Each WS In ActiveWorkbook.Worksheets
With WS
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For X = LastRow To 1 Step -1
If .Cells(X, 1).Value = "" Then
.Rows(X).Delete
End If
Next X
End With
Next WS
End Sub

A few comments: You will find your life much better if you ALWAYS declare
your variables using the Dim statement. You can enforce this practice by
including Option Explicit as the very first line in the module, before and
outside of any Sub or Function procedure. This can be automatically set by
checking the "Required Variable Declaration" in the "Editor" tab of the
"Options" dialog from the "Tools" menu in VBA. See also
http://www.cpearson.com/Excel/variables.htm.

Next, you (almost) never need to Select or Activate anything in VBA. Doing
so only causes the code to run slower and is, in my opinion, more difficult
to maintain.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Sue" wrote in message
...
I am trying to use code that was listed in a previous newsgroup posting to
remove blank lines on a worksheet. Is there a way to set this up as a
macro
that would work for all worksheets in the workbook? I am not very
knowledgeable with VB language. Below is the posting I have followed so
far.
It works for 1 worksheet, but not for the entire workbook.

Thanks for any help.

I have assumed that if the cell in column A is empty then this identifies
a
blank row. If that is correct then right click the sheet tab, view code
and
paste this in:-

Sub surface()
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For x = LastRow To 1 Step -1
Cells(x, 1).Select
If Cells(x, 1).Value = "" Then
Selection.EntireRow.Delete
End If
Next
End Sub