ClearContents problem (SOLVED)

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.)
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,222,037
Messages
6,163,539
Members
451,843
Latest member
vitto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top