SHORTCUT TO DELETE ALL BLANK ROWS ON A WORKSHEET?

[Guest]

Is there a way to use Excel to move to the end or deleteentirely the blank
rows on a worksheet. I was think of find and replace as a possibliity if
there is a standard machine language name for empty rows. The alphabetical
way works. Perhaps adding a column of consecutive numerals adjacent to the
data; sort the original column and delete the space rows and then resort the
numeric column and delete it with all your empty rows deleted. Glad I asked.
Loren Liddell

 

[David McRitchie]

Hi Loren,
see http://www.mvps.org/dmcritchie/excel/delempty.htm

The following macro will select the cells in column A that are empty and
check each row with that empty cell to see if there is content in any of the
cells in those rows. SpecialCells is by definition limited to the used range.

Sub DelEmptyRows()
Dim chkRange As Range, i As Long, iLimit As Long
Set chkRange = Columns("a").SpecialCells(xlCellTypeBlanks)
iLimit = chkRange.Count
'-- not working yet ----
If iLimit = 0 Then Exit Sub
For i = iLimit To 1 Step -1
If Application.CountA(chkRange.Item(i).EntireRow) = 0 _
Then chkRange.Item(i).EntireRow.Delete
Next i
iLimit = ActiveSheet.UsedRange.Rows.Count 'attempt to fix lastcell
ActiveWorkbook.Save
End Sub

To install the macro see
http://www.mvps.org/dmcritchie/excel/getstarted.htm
You can assign a shortcut if you want, or run from tools, macro (Alt+F8)