Range Error
 

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.

Range Error
 

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.

Range Error
 

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.

Range Error
 

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.

Range Error
 

Duh. Offset and resize make sense heh. And I always try to use one
liners to avoid variables whenever possible. I guess its just habit
to avoid declaring variables unnecessarily as it uses memory. I guess
not a huge concern with excel macros, but habit from old C programming.


Die_Another_Day wrote:
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.

Range Error
 

What's your druthers: memory or speed or money

Every period in a command has a speed penalty

How about maintenance - if the command runs off the end of the screen, how
much harder is it to maintain/understand. Increased time means increased
cost.

and variables do require memory.

All that said,

is the expense significant. That's why they pay you the big bucks. "You
make the call."

Sometimes With/End With is a good compromise

--
Regards,
Tom Ogilvy

"John Fuller" wrote:

Duh. Offset and resize make sense heh. And I always try to use one
liners to avoid variables whenever possible. I guess its just habit
to avoid declaring variables unnecessarily as it uses memory. I guess
not a huge concern with excel macros, but habit from old C programming.


Die_Another_Day wrote:
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.

Range Error
 

Tx Tom!

Charles
Tom Ogilvy wrote:
What's your druthers: memory or speed or money

Every period in a command has a speed penalty

How about maintenance - if the command runs off the end of the screen, how
much harder is it to maintain/understand. Increased time means increased
cost.

and variables do require memory.

All that said,

is the expense significant. That's why they pay you the big bucks. "You
make the call."

Sometimes With/End With is a good compromise

--
Regards,
Tom Ogilvy

"John Fuller" wrote:

Duh. Offset and resize make sense heh. And I always try to use one
liners to avoid variables whenever possible. I guess its just habit
to avoid declaring variables unnecessarily as it uses memory. I guess
not a huge concern with excel macros, but habit from old C programming.


Die_Another_Day wrote:
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.