Deleting Empty Rows

CATEGORY - Excel Importing Text Files VERSION - All Microsoft Excel Versions To delete empty rows between data:

1. Select all columns containing data.
2. Click the Sort icon (either Ascending or Descending).

Screenshot // Deleting Empty Rows



Book Store:
Excel VBA books
Accounting books
Business Plans
MS Office books
Taxes books
VIEW ALL BOOKS
Recommended Books:
The McGraw-Hill 36-Hour Course in Finance for Nonfinancial Managers
Seven Habits Of Highly Effective People
Microsoft Access 2002 for Dummies
Personal Finance for Dummies
Excel 2002 For Dummies®
PowerPoint® 2002 For Dummies®
Email Tip Print TipAdd to My TipsRate This Tip

1 2 3 4 5



Rating: 2.78 Views: 183008
alternative
Bhagyesh
we can use auto filter and delete the rows if we dont want to sort
F5 key
Nimrod
Selecting certain types of cells is wht the "Goto" F5 key is for. Just Slect the columns in question , press F5 and then select "blanks" in the Goto window. Now only the blank cells are selected. Once Selected you can do anything you want with them ... eg right click and delete , or bold , or what ever.
This not work when cell is containg untype characters
like NULL or formula ="" or something that similar but it is not really enpty cell. Very oftet in occure when copy from NULL from access by CTR + C and CTR + V. Export table feature work correctly and instead of NULL return really empty cell.
Macro to delete rows
Gary L Brown
'/============================/
' Sub Purpose: Delect all blank ROWS within the active cell's
' Used Range
'
Public Sub DeleteBlankRows()
Dim dbMaxRow As Double, dbMinRow As Double, i As Double
Dim dbMaxCol As Double
Dim rng As Range

On Error Resume Next

'only look in used area of the worksheet where active cell is
Set rng = Selection.Parent.UsedRange

'calculate area to be searched for blank rows
dbMaxRow = rng.Rows.Count '# of rows in used area
dbMinRow = rng.Cells(1, 1).Row '1st row in used area
dbMaxCol = rng.EntireColumn.Count '# of columns in used area

For i = dbMaxRow To dbMinRow Step -1
If IsError(rng.Cells(1, 1).Offset(i - 1, 0).EntireRow. _
SpecialCells(xlCellTypeBlanks).Count) Then
Else
If rng.Cells(1, 1).Offset(i - 1, 0).EntireRow. _
SpecialCells(xlCellTypeBlanks).Count = dbMaxCol Then
rng.Cells(1, 1).Offset(i - 1, 0).EntireRow.Delete
End If
End If
Next i

Set rng = Nothing

End Sub
'/============================/
'
Pitfall
Balkee
Depending on the contents of the first column in the range, the sorting will cause the order of rows to be different from the original order. After you delete the blank rows, it may be a bigger hassle getting back to the original order. Use filter as Bhagyesh suggested.
I found that
Rhobbynho
'Matt Neuburg, PhD http://www.tidbits.com/matt Aug , 1998

Sub DelEmpty()
Selection.SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp)
End Sub
Agreed
Ade
Really no need to use sort instead of go to. A macro is a good idea if you want to include it in the code you write.
Easier than that
c7borg
just press f5 select special and check the "blanks" radio button then simply select edit>delete

job done
Alternative to deleting blank rows
Roger Morehouse
ALthough I agree that AutoFilter works great for some worksheets, a large worksheet will bog down using this method. If you need to maintain the order of the rows, insert a new column, fill with numbers, sort by desired column, delete blank rows, sort by number column, delete number column.
Alternate to Sorting, then deleting
Roger Morehouse
For large worksheets, another method is to insert a column, then fill with numbers. Sort by the desired column, delete the rows, resort by the number column. But I agree, Filter is good for small worksheets.
AWESOME
dude
you are so good with the Macro - i took 45 mins to do this manually in the morning - and your macro did it in 3 seconds flat -- THANK YOU FOR THIS