Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ClearContents with pivot table

Posted on 1999-01-20
7
Medium Priority
?
482 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
New style of hardware planning for Microsoft Exchange server.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

783 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