Archive of Mr Excel Message Board

Back to Pivot Tables in Excel archive index
Back to archive home

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?

Re: Deleting blank rows in Excel pivot tables

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

Missing (

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

Whoops:

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

Depends on what Blank Output looks like

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

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?

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.