Solved

ClearContents with pivot table

Posted on 1999-01-20
7
424 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 2

Accepted Solution

by:
cartti earned 100 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now