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.

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
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"danw" <danw@discussions.microsoft.com> wrote in message
news:D80B1C41-A35D-4C0E-B790-D314AAE45C10@microsoft.com...
>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.

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" <danw@discussions.microsoft.com> wrote in message news:D80B1C41-A35D-4C0E-B790-D314AAE45C10@microsoft.com...
>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.

Dan

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

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"danw" <danw@discussions.microsoft.com> wrote in message
news:D80B1C41-A35D-4C0E-B790-D314AAE45C10@microsoft.com...
>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.