Range resize not working

[Ray Batig]

Greetings,

I wrote the following code to automatically resize a worksheet range that
grows vertically.

With Range("RegionWorkingData").Resize(1, 1)
.Parent.Range(.Item(1), .Parent.Cells(Rows.Count, .Column) _
.End(xlUp)).Name = "RegionWorkingData"
End With

I can't seem to get it to work. When I run it I get the error message that
the Method 'Range of object'_Global' failer.

Can some one help me fix this code?


Thanks in advance for your help and guidance!!

Ray

 

[Dave Peterson]

Maybe specifying the worksheet would help:

with worksheets("sheet99").range("regionworkingdata")....

If that code is behind a worksheet, excel will assume that "regionworkingdata"
is a range on that sheet that owns the code.

 

[Ray Batig]

Thanks Dave,

I added the worksheet reference as you suggested, however, I get the same
error. This macro is in a module and is activated by a button on a
worksheet. Any more ideas?

Thanks again for your help!!

Ray

 

[Dave Peterson]

Just the common suggestion: Check the spelling of the range name.

Your code worked ok for me. But I did need the qualifier when the code was
behind the worksheet.

 

[Dave Peterson]

One more thought (from a very similar post):

Are you running xl97 and calling the procedure from a control from the control
toolbox toolbar (placed on a worksheet)?

If that's the case, change the .takefocusonclick property to false for that
control.

Show the control toolbox toolbar.
click on the design mode icon
rightclick on the control
choose properties
change .takefocusonclick to false
click on the design mode icon (to get out of that mode)

Alternatively, if that control doesn't have that property, you could add this to
the top of your procedure:

Activecell.activate

(This bug was fixed in xl2k.)

 

[Ray Batig]

Dave, that one fixed it. It was an old workbook that had been built in 97.
Looks like the characteristic hangs in there no matter what version you are
currently running.

Thanks for your help!

Ray