Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

ClearContents with pivot table

Posted on 1999-01-20
7
438 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
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 …

808 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