Tom, are there any memory/performance hits when you add a variable? The
only reason I tend to shoot for 1-liners is to avoid creating more
variables. In this case however I would go with your approach, but was
trying to show the OP what was keeping his code from working.

Charles

Tom Ogilvy wrote:
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.