ExcelBanter - Delete row if all cells are blank, Excel 2000 & 2003

Try this one

For more info see
http://www.rondebruin.nl/delete.htm

Sub Loop_Example()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'We use the ActiveSheet but you can replace this with
'Sheets("MySheet")if you want
With ActiveSheet

'We select the sheet so we can change the window view
.Select

'If you are in Page Break Preview Or Page Layout view go
'back to normal view, we do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

'Turn off Page Breaks, we do this for speed
.DisplayPageBreaks = False

'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1

If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete

Next Lrow

End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

--

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

wrote in message ...
Hello,

I have a worksheet with 1061 rows and 7 columns of data.

Example:
column A B C D E
aa bb cc
vv
mm
blank row
ss

I'm need a macro to delete only the blank rows. I've tried the
following macro but it deletes a row when it finds a empty cell. Can
this macro be modified to delete a row with all empty cells only?

Sub DeleteRowsExampleB()
'Delete Selected Rows with specified data
Dim c As Range
Dim x As Range
Set x = Selection
For Each c In x
If c.Value = "" Then
c.EntireRow.Delete
End If
Next c
End Sub

Thank you for your help,
jfcby