You need to fully qualify the Cells reference:

With Worksheets("NotInStock")
.Range("A2", .Cells(Rows.Count, "A")).Clear
End With

Note the . in front of Cells

John Green



"Ade" wrote in message
...
I use the following code to delete cells from the Active Worksheet,

Range("A2", Cells(Rows.Count, "A")).Clear

this works fine but when I try to delete cells from a different sheet in
my
Workbook I get 'Application-defined or Object-defined Error'.
The code which gives this error is,

Worksheets("NotInStock").Range("A2", Cells(Rows.Count, "A")).Clear

any suggestions as to what is wrong with the code?
Thanks.