ClearContents problem (SOLVED)

[phantom1975]

I have this simple macro:

Sheets("Sheet1").Range("A3:A1000").ClearContents

When I try to run this macro, I get this error message:

Run-time error '1004':

ClearContents method of Range class failed

These are not protected cells, so I have NO idea what the issue is. Any ideas?

_________________
Pass on what you have learned. Support this great website by clicking on the sponsor's ads!.
This message was edited by phantom1975 on 2002-10-11 00:32

 

[Andrew Poulsom]

Some thoughts:

1. How can you have a sheet named "Daily Numbers" that in the Project window is named "Daily Activites"?
2. If you can do it manually, record it. What happens when you run the recorded Macro?

The more I read the more I think this is just a problem with names - Daily Numbers, Daily Activities, Today's Activities - too many Dailies and Activities.

 

[phantom1975]

The CommandButton was placed directly on the sheet called, "Today's Activities" using the Control Toolbox toolbar. When clicked, it should delete data that is on the "Daily Numbers" sheet.

When I record a macro and change the CommandButton_Click procedure to call the macro, it works fine. When I copy and paste the macro procedure into the CommandButton_Click event and delete the Call Macro1, it fails. It continues to fail at the same point. Selecting the range A3:A1000. I'd really like to know what the heck this might be.

 

[Krupa]

Hi

I think i have gotten this error before. I cannot remember exactly how i fixed it but ...
Here are a few suggestions.

Try this:

Sheets("Daily Numbers").Select
Range("A3:A1000").ClearContents

If that doesn't work the check to see if the sheet is hidden. Try

Sheets.("Daily Numbers").visible=true
Sheets("Daily Numbers").Range"A3:A1000").ClearContents

Excel may not like it if you try to clearcontents on a hidden sheet.

Hope that helps.
I'll see if I can remember how I fixed it.
Let me know if it works.

Krupa

 

[Krupa]

hey,

i just thought of something else. Is the page locked or anything like that?
this could affect you clearing in the code.

hopethishelps,
Krupa

 

[phantom1975]

Already tried all of that Krupa. This one is a COMPLETE mystery to me.

 

[Juan Pablo González]

Are you sure you read Ivan's suggestion ?

if yes, then try setting this

ActiveCell.Activate

before the ClearContents statement.

 

[phantom1975]

I took Ivan's advice and got changed the property to false. I added ActiveCell.Activate before the clear contents and still no luck.

 

[stevebausch]

Phantom,

The worksheet is corrupted, save what you can, and start a replacement sheet.

Steve

 

[phantom1975]

I moved everything to a new sheet and it did the trick. I've never heard of corrupt worksheets before, but then again, there is a lot I haven't heard of yet! Thanks for all the help.

 

[ddavydenko]

I got the same problem. Was struggling with it but then debugged through the code and got more detailed message about merged cells being partially in the range I was trying to clear and partially out of the range. So I changed the range to include the merged cells completely and then this error went away. Hope this helps to someone.