an additional approach

I have tested this and it seems to work well:

Sub abc()
Dim rng As Range, NumRowPN As Long
NumRowPN = 7
Set rng = ThisWorkbook.Names("RunTime6").RefersToRange(1)
Set rng = rng.Offset(1, 0).Resize(NumRowPN + 1, 1)
rng.ClearContents
End Sub


You could make it all one big line of code, but why struggle?


--
Regards,
Tom Ogilvy


"Die_Another_Day" wrote:

The problem is the .Range(Cells(... statements. You need to give cells
a sheet object to reference to if you are not working on the active
sheet. for instance you could use .Range(Sheets("Sheet2").Cells(...
Clear as mud?

HTH

Charles Chickering

John Fuller wrote:
I get a Run time error - application or object-defined error if I run
the followign code with an active sheet other than the one the range is
on (NumRowPN = 7 atm):
ThisWorkbook.Names("RunTime6").RefersToRange.Range ("A1").Range(Cells(2,
1), Cells(1 + NumRowPN, 1)).ClearContents

Any help is appreciated. And I realize I can just activate the sheet
first but I would prefer not to. Thanks.