• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 535
  • Last Modified:

ClearContents with pivot table

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
taraleigh
Asked:
taraleigh
  • 4
  • 3
1 Solution
 
carttiCommented:
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
 
taraleighAuthor Commented:
But why would it stop clearing the data in 97 when it worked in 7.0?
0
 
taraleighAuthor Commented:
But why would it stop clearing the data in 97 when it worked in 7.0?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
carttiCommented:
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
 
taraleighAuthor Commented:
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
 
carttiCommented:
How about replacing Selection.ClearContents with Selection.Delete ? You could also condense the two lines of code to one by using Cells.Delete.
0
 
taraleighAuthor Commented:
Thankyou.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now