How to delete all blank rows in a sheet?

[Gregg Hill]

Hello!

When I paste or import text from a CSV into an Excel sheet and then edit it,
I often end up with dozens of blank rows in the middle of sections of
populated rows. Is there an easy way to find and delete all blank rows in a
spreadsheet or range of rows? The only way to do it right now is a very time
consuming Ctrl-click to highlight a bunch and then Edit, Delete them.

Thanks for helping!

Gregg Hill

 

[J.E. McGimpsey]

If there's a column in your data that is always populated if there's
data in the row, select the column, choose Edit/Goto/Special, check
the "blank" checkbox, then click OK. Select Edit/Delete and choose
Entire Row from the popup.

You can use a macro to do the same thing:

Public Sub DeleteBlankRows()
On Error Resume Next
Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub


If your rows may have blanks in any column, you'll have to use a
macro to go line by line. Here's one way:

Public Sub DeleteBlankRows2()
Dim rRow As Range
Dim rDelete As Range
For Each rRow In ActiveSheet.UsedRange.Rows
If Application.CountA(rRow) = 0 Then
If rDelete Is Nothing Then
Set rDelete = rRow
Else
Set rDelete = Union(rDelete, rRow)
End If
End If
Next rRow
If Not rDelete Is Nothing Then _
rDelete.EntireRow.Delete
End Sub

 

[wiseman]

John Walkenbach has an add in all ready to download. I use it a lot for
data copied from a web site which has blank rows in it when copied to excel.
Look for
J-Walk Conditional Row Delete
on this page
http://www.j-walk.com/ss/excel/files/general.htm
Barbara.

 

[Brian Tozer]

John Walkenbach has an add in all ready to download. I use it a lot
for data copied from a web site which has blank rows in it when
copied to excel. Look for
J-Walk Conditional Row Delete
on this page
http://www.j-walk.com/ss/excel/files/general.htm

On that page I see the following:-

Download the J-Walk Conditional Row Delete add-in (73K)
An enhanced version of this utility is available in Power Utility Pak.

I have PUP5 installed but have been unable to find the CRD anywhere in it.
Can someone point me towards it?

Thanks
Brian Tozer

 

[wiseman]

Brian
I don't know anything about PUP as I have not downloaded it but the row
delete which you download on its own works a treat.
Barbara

 

[Gregg Hill]

That is awesome, thank you very much! Why didn't MS include something so
simple? I have Excel 2002 and it can't even do it without the add-in! Thanks
for the link to that incredible time-saver.

Gregg Hill

 

[Gord Dibben]

Gregg

MS did include something so simple..

Highlight a column....Edit>Go To>Special>Blanks>OK. Delete>Entire Row.

Gord Dibben Excel MVP

 

[Gregg Hill]

Gord,

Geez, I searched the help files for along time and could not find that
method. Thanks! However, I must be missing something. Where is the option to
delete the entire row? I am using Excel 2002 from Office XP Pro.

Gregg Hill

 

[Gregg Hill]

Gord,

Never mind! I missed the part of your post about highlighting the column
first. That is awesome! Go MS! I sit corrected.

Gregg Hill

Gord,

Geez, I searched the help files for along time and could not find that
method. Thanks! However, I must be missing something. Where is the option to
delete the entire row? I am using Excel 2002 from Office XP Pro.

Gregg Hill

Gregg

MS did include something so simple..

Highlight a column....Edit>Go To>Special>Blanks>OK. Delete>Entire Row.

Gord Dibben Excel MVP