Deleting empty rows in a worksheet
 

I want to use a macro to delete out all empty rows in a spreadsheet. There
are 1,700 rows and 10 columns. If all of the cells are blank in any of the
rows, I want to delete that row. I am fairly new with VBA.

Deleting empty rows in a worksheet
 

Dan

Is the entire row blank? If so can it not just be sorted?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"danw" wrote in message
...
I want to use a macro to delete out all empty rows in a spreadsheet. There
are 1,700 rows and 10 columns. If all of the cells are blank in any of
the
rows, I want to delete that row. I am fairly new with VBA.

Deleting empty rows in a worksheet
 

Hi danw

Try this one

Sub Example2()
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim StartRow As Long
Dim EndRow As Long

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

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = 1700

For Lrow = EndRow To StartRow Step -1

If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete
'This will delete the row if the whole row is empty (all columns)

Next
End With

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

End Sub


There are more examples here
http://www.rondebruin.nl/delete.htm




--
Regards Ron de Bruin
http://www.rondebruin.nl


"danw" wrote in message ...
I want to use a macro to delete out all empty rows in a spreadsheet. There
are 1,700 rows and 10 columns. If all of the cells are blank in any of the
rows, I want to delete that row. I am fairly new with VBA.

Deleting empty rows in a worksheet
 

Dan

Or you could use something like this, with blanks in A

Sub delRows()
Range("A:A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"danw" wrote in message
...
I want to use a macro to delete out all empty rows in a spreadsheet. There
are 1,700 rows and 10 columns. If all of the cells are blank in any of
the
rows, I want to delete that row. I am fairly new with VBA.