Deleting Blank Rows

Hello, Can some tell me the code to use to delete blank rows of an excel file?

I thought I could create a loop to start at the first row and proceed
through the file and deleting the blank rows, but am having difficulty 1)
systematically identifying and deleting the blank rows and 2) stopping the
loop at the end of the file.

Any Help Would Be Appreciated - Thank You

Dim rng as Range
set rng = Columns(1).specialCells(xlBlanks)
if not rng is nothing then
rng.entirerow.Delete
End if

This assumes that the data in the first column can be used to determine
which rows are blank. Also that you do not have more then 8192 separate
(non-contiguous) areas in the first column.

--
Regards,
Tom Ogilvy

"MWS" <[email protected]> wrote in message
news:[email protected]...
> Hello, Can some tell me the code to use to delete blank rows of an excel
file?
>
> I thought I could create a loop to start at the first row and proceed
> through the file and deleting the blank rows, but am having difficulty 1)
> systematically identifying and deleting the blank rows and 2) stopping the
> loop at the end of the file.
>
> Any Help Would Be Appreciated - Thank You

MWS, here is one way,

Sub Delete_blank_rows()

'Will delete the whole row where there are blank cells in A1:A50

[A1:A50].SpecialCells(xlCellTypeBlanks).EntireRow.Delete

End Sub
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003


"MWS" <[email protected]> wrote in message
news:[email protected]...
> Hello, Can some tell me the code to use to delete blank rows of an excel
> file?
>
> I thought I could create a loop to start at the first row and proceed
> through the file and deleting the blank rows, but am having difficulty 1)
> systematically identifying and deleting the blank rows and 2) stopping the
> loop at the end of the file.
>
> Any Help Would Be Appreciated - Thank You