MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Deleting blank rows in Excel pivot tables


Posted by Rich Anderson on January 14, 2002 3:29 PM

I have a fairly large excel pivot table with blank rows between each valid data record/row. Could someone suggest a procedure/utility/macro to automate the process of deleting these blank rows?


Posted by Nate Oliver on January 14, 2002 3:44 PM

If you change the range, try the following:

sub delete1()
Range("a1:a20000").Select
Selection.SpecialCellsxlCellTypeBlanks).EntireRow.Delete
end sub

Hope so. Cheers!

Nate

Posted by Nate Oliver on January 14, 2002 3:46 PM

Missing (

Whoops:

sub delete1()
Range("a1:a20000").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
end sub

Posted by Nate Oliver on January 14, 2002 4:10 PM

Depends on what Blank Output looks like

I had a blank output cell = "(blank)"

In this case my previous response did not work. But this did:

Sub delete1()
Range("a10:a40").Select
For Each cell In Selection
If cell = "(blank)" Then cell.Delete
Next
End Sub

I have a fairly large excel pivot table with blank rows between each valid data record/row. Could someone suggest a procedure/utility/macro to automate the process of deleting these blank rows?