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 Removed)
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
"Joyce" wrote:
> 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" wrote:
>
> > 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.
> >
> > --
> > Ryan---
> > If this information was helpful, please indicate this by clicking ''Yes''.
> >
> >
> > "Joyce" wrote:
> >
> > > 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
Similar Threads
