Hi Steffi,

You need fully to qualify the ranges, e.g.:

With ThisWorkbook.Worksheets("shname")
.Range(.Cells(2, colNo1), .Cells(2, colNo2)).ClearContents
End With

If you do not qualify the Cells expressions, the code will assume that the
cells relate to the active sheet. If the active sheet is not the shname
sheet this will cause the run time 1004 error as it is not possible to
construct a range on the shname sheet which comprises cells from another
sheet.

Of course, if the code were run whilst shname was active, no error would
ensue. It remains good practice, however, always fully to qualify ranges.


---
Regards,
Norman



"Stefi" wrote in message
...
Hi All,

I wanted to use
ThisWorkbook.Worksheets("shname").Range(Cells(2, colNo1), Cells(2,
colNo2)).ClearContents

but it failed with a runtime error 1004.

When I changed the code either to
ThisWorkbook.Worksheets("shname").Select
Range(Cells(2, colNo1), Cells(2, colNo2)).ClearContents

or - having transformed colNo1 to colLetter1 and colNo2 to colLetter2 - to
ThisWorkbook.Worksheets("shname").Range(colLetter1 & 2 & ":" & colLetter2
&
2)).ClearContents

both version worked. I'd like to know WHY this happens, is it my fault or
is
it an XL bug?

Regards,
Stefi