Pivot Table with Blank Rows - HELP
 

I have a spread sheet that has over 12000 rows. When I try to do my
PivotTable, I keep getting an error message. The reason I think I get the
message is that every other row in my worksheet is blank. Is that a problem
and if it is, is there a way that I can delete these rows without having to
do it one by one and selecting them all, there are over 12000 rows so it
would be very time consuming. Any help would be appreicated.
--
Sandi Gardner

Pivot Table with Blank Rows - HELP
 

This should get rid of all your blank rows. Make sure you back up your file
before trying anything though, just in case. If this solves the problem then
great, if not we'll have at least narrowed the problem down a bit. Just
change the sub so that instead of cells (mylastrow - 1, 1) you have
(mylastrow - 1, *) where the * is whichever column you want checked.

Sub test()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim mylastrow As Long
mylastrow = Cells.Find("*", After:=Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
While mylastrow 1
If Cells(mylastrow - 1, 1).Value = "" Then Rows(mylastrow - 1).Delete
mylastrow = Cells.Find("*", After:=Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
mylastrow = Cells(mylastrow, 1).End(xlUp).Row
Wend

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
cheers,

Scott

"Sandi" wrote:

I have a spread sheet that has over 12000 rows. When I try to do my
PivotTable, I keep getting an error message. The reason I think I get the
message is that every other row in my worksheet is blank. Is that a problem
and if it is, is there a way that I can delete these rows without having to
do it one by one and selecting them all, there are over 12000 rows so it
would be very time consuming. Any help would be appreicated.
--
Sandi Gardner

Pivot Table with Blank Rows - HELP
 

or...

sort the data so that the blank rows are at the bottom, and then have
the pivot table data range only to last "used" row.


--
mr_ben
------------------------------------------------------------------------
mr_ben's Profile: http://www.excelforum.com/member.php...o&userid=37106
View this thread: http://www.excelforum.com/showthread...hreadid=568419