Delete Blank Rows when Pivot Table included on sheet

[Joyce]

Hi,

I've run code to delete blank rows which works fine. That is, it works fine
until I'm deleting blank rows on a sheet which also contains a few pivot
tables.

I encounter an error message regarding not being able to change pivot table.

Here is my code: Thanks!

Dim x As Long

With ActiveSheet
For x = .Cells.SpecialCells(xlCellTypeLastCell).Row _
To 1 Step -1

If WorksheetFunction.CountA(.Rows(x)) = 0 Then
ActiveSheet.Rows(x).Delete
End If

Next
End With

 

[ryguy7272]

You can't delete or add rows to a Pivot Table. Delete all blanks before you
build the Pivot Table (I assume you are building the table with code, right).
Or, in your Pivot Table you will see some down arrows, click any one and if
there are blanks in there you'll see (blank) with a small check box in front
of it. Un-check the box and that will eliminate the blanks. Then, turn on
the macro recorder and do the same thing and turn off the recorder. Look at
the resulting code. Copy/paste that code into the appropriate place in your
code-sequence, and Excel will automatically do this for you next time
(eliminate blanks). You may have to play with the positioning of the code a
bit to get it working right, but just keep at it and you'll get it.

 

[Joyce]

Hi Ryan,
Thanks so much for your response. Here is my problem in more detail. I
have a report that contains many pivot tables that are positioned vertically
one beneath the other. There are report headings and information above the
pivot tables as well.

I don't want to overwrite the text between the pivot tables as the pivot may
expand. At the moment, the only solution I can see is to add a bunch of rows
under each pivot table so that refreshing them based on updated data and
different Page Field selections won't overwrite anything.

Unfortunately, though, it means I end up with many, many rows very often
that remain beneath various pivot tables. I'd like to have an easy way to
remove these. I had used placeholders on rows I don't want removed and run
my code to delete blank rows that I've used often in other reports that don't
contains pivots, but get a runtime error 1004 when try to run it in one that
does.

Is there a way to add something that says, in essence, "ignore pivot
tables"?

Any suggestions are very appreciated.

 

[ryguy7272]

Sounds somewhat complex, but equally interesting. I have some free time in
the next day or so to look at this. If you send me a before and after
example, I'll try to reply back with a solution within the next day or so.
Thanks!!
(e-mail address removed)