?
Solved

ClearContents with pivot table

Posted on 1999-01-20
7
Medium Priority
?
503 Views
Last Modified: 2008-03-17
I have been running the following in line of code in Excel 7.0 but when I convert it into Excel 97 it fals over.

Sheets("Country Act Pvt").Select
    Cells.Select
    Selection.ClearContents THIS LINE DOES NOT WORK

Part of the area selected is a Pivot table.  The error message "Run time Error 1004.  Cannot change this part of a Pivot Table"

Could anyone help?
0
Comment
Question by:taraleigh
  • 4
  • 3
7 Comments
 
LVL 2

Expert Comment

by:cartti
ID: 1615820
I don't think it has anything to do with the conversion.

The only explanation is the pivot table must have been made up from data in another pivot table in another sheet/ workbook. Otherwise if there were no links there would be no problem clearing the contents.
0
 

Author Comment

by:taraleigh
ID: 1615821
But why would it stop clearing the data in 97 when it worked in 7.0?
0
 

Author Comment

by:taraleigh
ID: 1615822
But why would it stop clearing the data in 97 when it worked in 7.0?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 2

Accepted Solution

by:
cartti earned 400 total points
ID: 1615823
Fair point, in version 7.0 it does allow data to be cleared from another pivot table even if it is linked to another pivot table. In Excel 97 it doesn't. This must be a barrier that was introduced to 97 which prevents pivot tables from displaying invalid data.

I'm not having a stab in the dark about this as I guessed correctly about more than one pivot table used, no ?
0
 

Author Comment

by:taraleigh
ID: 1615824
I agree you have the answer to why the area cannot be cleared but is there another way of clearing the data without losing the formatting?  I was looking for a solution rather than just the reason why it would not work.

I have not rejected your answer and I will accept it if you have another method to use.
0
 
LVL 2

Expert Comment

by:cartti
ID: 1615825
How about replacing Selection.ClearContents with Selection.Delete ? You could also condense the two lines of code to one by using Cells.Delete.
0
 

Author Comment

by:taraleigh
ID: 1615826
Thankyou.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
I came across an unsolved Outlook issue and here is my solution.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question