ClearContents not working

SGBCleve

New Member
Joined
Dec 4, 2011
Messages
35
I am trying to run a Macro in Sheet1 which worked perfectly before. For reasons unknown to me, it is not working tonight and I get an error at the line ClearContents, RunTime Error 1004, ClearContents Method of RangeClass failed. As an aside, I am also having problems getting my computer to enter quotation marks while in the VBA editor. I have to put a space or a second character for the quote mark to show up and "I comes out as an umlaut over the I. It doesn't happen in other programs. I am stumped.


Dim rrow As Integer
Dim j As Integer
Dim k As Integer
Dim z As Integer
Dim holding(50)
Dim Sum As Integer
Dim i As Integer
Dim wrkRng As Range
Dim MaxRng As Range
Dim MinRng As Range
Dim n As Integer
Dim y As Integer






Worksheets("Sheet1").Select
Set wrkRng = Worksheets("Sheet1").Range("A1:B10")
Set MaxRng = Worksheets("Sheet1").Range("A20:A29")
Set MinRng = Sheets("Sheet1").Range("B20:B29")

Sheets("Sheet1").Activate
wrkRng.ClearContents


MaxRng.ClearContents
MinRng.ClearContents

If I put apostrophe in front of the the three range.ClearContents statements, the code runs to the next ClearContents statement and I get the same error.

Sheets(i).Select
With Sheets(i)
.Range("G3:I11").ClearContents

.Range("J3:L11").ClearContents

.Range("G2:G11").Value = Sheets("Sheet1").Range("A20:A29").Value
.Range("J2:J11").Value = Sheets("Sheet1").Range("B20:B29").Value
 
Last edited:

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
In addition, a black bordered box is showing up, except it is not always in line with a cell, and cells I select are not highlighted. :confused::confused:
 
Upvote 0
Maybe the sheet is protected? You can use UserInterfaceOnly:=True for Sheet1.Protect option so that code can make changes. I normally do that for all worksheets in Thisworkbook's Open event.

Select, Activate, Selection, and such are seldom needed.
 
Upvote 0
You need a End With statement like this:
Code:
With Sheets(i)
 .Range("G3:I11").ClearContents

 .Range("J3:L11").ClearContents

 .Range("G2:G11").Value = Sheets("Sheet1").Range("A20:A29").Value
 .Range("J2:J11").Value = Sheets("Sheet1").Range("B20:B29").Value
End With
 
Upvote 0
It seems to have been a problem with my computer. I closed Excel and restarted it, and now it's working. Not sure why. The sheet was not protected, and the End With statement was further down, but thanks for the help
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

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