PDA

View Full Version : [SOLVED] Clearcontents method of range class failed



fredlo2010
05-15-2014, 11:44 AM
Hello guys,

I have a problem with a workbook and I would like to see if anyone else has ever seen this. I get the error "excel clearcontents method of range class failed" every time my code goes beyond a specific amount of rows.

The sheet contains several Index- Match-Match formulas. I am using Excel 2010.

Any ideas ?

I have tried the following:
-Reducing the formulas to cover smaller grounds.
-Put as many formulas as possible at once so its has to recalculate less.


If dups > 1 Then

If Left(Cells(7, i).Value, 6) = "LAM_" Then
Cells(start, i).ClearContents
Cells(start, i).Interior.ColorIndex = 28
Cells(start, i).FormatConditions.Delete
End If
End If

where dumps, start and i are integers.

Thanks a lot for the help :)

ranman256
05-15-2014, 11:57 AM
The CLEAR method will work IF you set a value to START. I dont see that here.

mancubus
05-16-2014, 04:39 AM
goes beyond a specific amount of rows.

i think i dont have the answer.

im asking below questions after a quick google search.

do you have that number which the code runs without causing the error?
and is the bit you posted a part of a normal procedure or an event procedure?

fredlo2010
05-16-2014, 06:07 AM
It's a regular code, no even driven. I don't know why but the problem was the amount of Index-Match-Match formulas. I replaced all the formulas by its values as the macro moved forward and that solved the issue. The row was 2057 how could I forget ! lol

I am guessing it was a little too much for Excel to handle.

Thanks for the replies. :)

Alfredo

Bob Phillips
05-17-2014, 03:03 AM
Can you post that workbook and tell us how to force the problem, I would like to see it in action.

fredlo2010
05-18-2014, 07:03 PM
hi,

Thanks a lot for the reply xld. Unfortunately I cannot port the workbook here not even removing sensitive data;. its a very complicated workbook. I solved the problem and I solved it by removing the lookup formulas that i am talking of about 3000 rows and about 15 columns filled with Index-Match formulas that included full columns and rows.

The macro loops through all the ranges and places the formulas one by one. After a certain condition is tested then the formula is either removed or kept.

I changed the formula to include ranges where data was present only so column("A:A") turned into Range("A1:A10000") which is already a relieve for the calculations. The since formulas are only used to retrieve data after I have the data I turned them into regular values; and finally I grouped similar formulas in two arrays so they are placed in the sheet in two loads. All of the above eliminated the problem.
I am pretty sure it was the formulas because when I dragged them to fill other columns and rows it would shoe in the bar "Calculating processors(2)" and took a long time to do that.

I tried to replicate the problem at home but I could not.

Thanks

Bob Phillips
05-19-2014, 01:25 AM
Okay, understood. Thanks.