Why does ThisWorkbook.Sheets.Range.Clearcontens give Applicaiton-defined or Object-defined error?

by joehua   Last Updated November 08, 2018 23:26 PM

I wonder why the following line gave "Applicaiton-defined or Object-defined error"?

If Sheet6 is selected, this line runs fine. No error message. If, instead, other sheet is selected, Excel throws the error message.

ThisWorkbook.Sheets("Sheet6").Range(Cells(1, 2), Cells(12, 1000).End(xlToLeft)).ClearContents

Answers 1

You need to qualify all your objects with your worksheet

With ThisWorkbook.Worksheets("Sheet6")
    .Range(.Cells(1, 2), .Cells(12, 1000).End(xlToLeft)).ClearContents
End With

You didn't qualify Cells() with your worksheet, so it pulls from the ActiveSheet instead of Worksheets("Sheet6").

So, remember that when your thinking "It only works when it's on that sheet" then this is going to be the issue.

November 08, 2018 23:20 PM

Related Questions

Median/average does not return the right values

Updated April 20, 2015 01:11 AM

Remove texts between two symbol, including identifiers

Updated November 07, 2017 12:26 PM

Solver.xlam is missing?

Updated December 04, 2017 02:26 AM

Excel List Data Validation

Updated July 11, 2018 20:26 PM