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

 

[stevebausch]

Do you have any of the Event routines filled with code? I am guessing an interaction.

What happens if you manually attempt the deletion?

You could break down that line into separate actions, see which action is truly causing the problem.

Sheets("Sheet1").activate 'See if this works
Range("A3:A1000").select 'How about this?
Selection.ClearContents 'Does this work?
This message was edited by stevebausch on 2002-10-10 02:38

 

[phantom1975]

I have also tried breaking it down and it still gives the same error. I am able to manually do the actions set about in the macro. I'm completely puzzled.

 

[Tom Urtis]

Just for the hell of it, try this line, assuming your sheet tab named "Sheet1" is the VBA sheet object named Sheet1.

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

 

[phantom1975]

The tab name is actually Daily Numbers and the code I have is actually:

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

In VBA, the sheet is called this withing the Microsoft Excel Objects

Sheet5(Daily Activites)

I tried Sheet5.Range("A3:A1000").ClearContents and that didn't work either. I seem to be having a lot of unusual problems with this spreadsheet!

_________________
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-10 02:55

 

[Tom Urtis]

Well there's something weird going on because both lines ought to work absent other obstacles. I'm sure you checked in your worksheet and workbook modules, and in standard modules, for any conflicting code as Steve suggested. Maybe data validation that requires a value to be present, or a minimum length of 1 in a cell in that range? There's got to be a reason.

 

[phantom1975]

I'm not sure if this helps, but I have this macro in a CommandButton on a different sheet. Here is the code:

Private Sub BtnClearAll_Click()
Sheets("Daily Numbers").Range("A3:A1000").ClearContents
End Sub

This CommandButton is on a sheet called "Today's Activities" using the Control Toolbox CommandButton (not the FORMS CommandButton.)

 

[stevebausch]

I think that Excel is a remarkably stable product, but Sometimes........things go wacky, and it's not possible to undo the wackiness.

For Example:
I just got done playing with selecting all sheets, using Ctrl key. When you select all the sheets, anything keyed on one sheet is echoed to all the sheets. Quite amazing.

When I got done experimenting, I tried to ClearContents on the affected cells. Sheet2 and Sheet3 were no problem, but Sheet1, A1 now had a persistent memory. New entries could be typed in, but ClearContents always brought back the "Does this work?" entry I had created with the mentioned technique.

As I say, Excel is pretty stable, but when it seems that the sheet is whacked, it probably is.

 

[Ivan F Moala]

Set your commandbuttons [TakeFocusOnClick] to False

 

[phantom1975]

That still doesn't take care of the problem. The error still occurs at the Sheets("Daily Numbers").Range("A3:A1000").ClearContents

It still occurs if I change it to Select and then Selection.ClearContents. Any other advice?