Method Clear Contents of Object Range Failed

[ardykav]

Hi
I have the following simple code, never had an issue with it before but all of a sudden I am getting the "Method Clear Contents of Object Range Failed" run time error. From what I have read this isnt so much an error with the code but more to do with the system is that correct? If anyone knows a way around this it would be greatly appreciated

Sub Newstepclean()

   
    Sheets("Calculations").Select
    Range("K2:N7000").Select
    Selection.ClearContents
    Range("K2").Select

End Sub

 

[VBE313]

Use this code instead.

Sub newStepClean()
Dim lastRow As Long, strCells As String
With Sheets("Calculations")
    .Activate
    lastRow = .Cells(.Rows.Count, "N").End(xlUp).Row
    strCells = "K2:N" & lastRow
    .Range(strCells).ClearContents
    .Range("K2").Select
End With
End Sub

 

[Fluff]

Is the sheet protected, or do you have merged cells within that range?

 

[ardykav]

Is the sheet protected, or do you have merged cells within that range?

No i checked all that and nothing like that in there, I have ran this code for months and nothing wrong with it and nothing has changed in the ranges mentioned in the code, however I have done some digging and it looks to be a more systematic failure, i tried the file on another Laptop and it works fine still. Anyone any ideas around this?

 

[mole999]

first questions have to be, are the laptops the same, do they all connect to you network the same, do they all have the same operating system and software for excel, the errant laptop, does it allow the user to make changes, have the all got the latest version or do you use a range of excels
you could shorten your code

Sub newStepClean()
    Application.EnableEvents = False
    Sheets("Calculations").Range("K2:N7000").ClearContents
    Application.EnableEvents = True
End Sub

 

[Fluff]

No idea why it would work on one computer, but not another. However does this work on both?

Sheets("Calculations").Range("K2:N7000").ClearContents

 

[ardykav]

No idea why it would work on one computer, but not another. However does this work on both?

Sheets("Calculations").Range("K2:N7000").ClearContents

No thats the thing, no variation of clear contents is working on both. As in on one all variations work and on another no variations work, If you take out that piece of code then it goes to another error "Automation error the object invoked has become disconnected from its clients"

 

[ardykav]

first questions have to be, are the laptops the same, do they all connect to you network the same, do they all have the same operating system and software for excel, the errant laptop, does it allow the user to make changes, have the all got the latest version or do you use a range of excels
you could shorten your code

Sub newStepClean()
    Application.EnableEvents = False
    Sheets("Calculations").Range("K2:N7000").ClearContents
    Application.EnableEvents = True
End Sub

Answers are no to all but the thing is they both worked fine up to a certain point and then just a few days ago the workbook stopped working on one which is really strange. There hasnt been any wholesale changes to either laptop or the workbook either.

 

[mikerickson]

Does

Sheets("Calculations").Range("K2:N7000").Value = vbNullString

do what you want?
How about .Clear.

No, I can't think of why this would be failing

 

[ardykav]

Does

Sheets("Calculations").Range("K2:N7000").Value = vbNullString

do what you want?
How about .Clear.

No, I can't think of why this would be failing

No, again I think its a system setting as any variation of clear contents works on one laptop but not another