Help with named range

[OverhandLeft]

Hello,

I have a macro that results in an ouput file of an excel spreadsheet.

There is a named range in the macro the clears data in the assigned range.

Let's say the range name is CLEAR_DATA with the range being
='Sheet2'!EG$78:EG$85,'Sheet2'!EG$92:EG$94

For some reason, when I run the macro to get the output file,
data is cleared from A78:A85 and A92:A94

Then I check the output file's range for CLEAR_DATA and I get
='Sheet2'!A$78:A$85,'Sheet2'!A$92:A$94

Would anyone happen to know what would cause this or how to
fix it?

As always, Thanks in advance to anyone that could help.

 

[jasonb75]

Post your macro code, without seeing it people will have to guess the problem.

 

[pgc01]

Hi

='Sheet2'!EG$78:EG$85,'Sheet2'!EG$92:EG$94

is a relative range. The columns can vary, depending on what cell is active. If you don't want it to change make it absolute:

='Sheet2'!$EG$78:$EG$85,'Sheet2'!$EG$92:$EG$94

 

[OverhandLeft]

PGC,

I tried absolute and I get a "range of object global failed.

It is not really critical that I clear that area of the spreadsheet

so I think I will just leave it be, without clearing it. Thanks anyway

 

[pgc01]

Well, if you don't need it you may skip it.

Anyway, I just tried it and had no problem

Sub Test()
 
' define the name
Names.Add "CLEAR_DATA", "='Sheet2'!$EG$78:$EG$85,'Sheet2'!$EG$92:$EG$94"

 
' populate the range
Range("CLEAR_DATA").Value = "X"
 
' clear the range
Range("CLEAR_DATA").Clear
 
End Sub