Autofit error

I'm using Excel 2002.
I just had a macro that has been working for months get an error

Error #: 1004
AutoFit method of Range class failed

This is the section of code that triggered the error.
All the variables are properly dimmed. I have verified that there was no error setting the value. The range is formatted as Number with 3 decimals. All the values are less than 11.

Please Login or Register  to view this content.

It has been working fine for several months.

Are there any reasons why autofit would fail? Help says it will fail only if r is not a column.
The only thing I can think of is that the width of the sheet may have hit some limit. I am using column IU

foxguy,

Let me think on this.

foxguy,

I am trying to duplicate your ranges...

Can you attach your workbook for testing?

I can't attach the file. It has proprietary data for a client.

I can't attach a mock file because it doesn't crash.

There has to be something specific to this file, but I can't figure out what it could be.

A few long shot guesses...

- Is the sheet protected?

- Are there any merged cells in the column?

- Was the file created in excel 2007 & "down saved" to the xls version (or is it being saved with compatibility mode)?
(I have no idea what this could relate to, but as I said, it's a longshot!)

- Are there any formulae returning an error in the column?
- Are there any hash symbols suggesting the column is not wide enough?
- Are any of the cells formatted as "shrink to fit"?
- Are there any "Custom Formats" used in the column?

- Are there any surprises if you include the below in your code?

Please Login or Register  to view this content.

hth
Rob

Thanks for the effort Rob.

All those suggestions produced no help. I've reached the conclusion that the file is corrupted, so I'm working on uncorrupting it now.

I am curious. How do you format a cell as "Shrink to Fit"? Is that an option in later versions of Excel?

hi Foxguy,

Try Rob Bovey's code Cleaner as part of the tidy up.

I think that selecting a cell, pressing [ctrl + 1], then choosing the Alignment tab will show "Shrink to Fit" in excel 2003, but I may be wrong - perhaps it is only in excel 2007?

Good Luck!

Rob

Hi Rob;

Code Cleaner didn't help. I copied all the sheets and code modules into a blank sheet, fixed all the links and the problem seems to be gone (knock on wood).

I couldn't find "Shrink to Fit" in 2003 or 2007, but I think autofit is just as good.

hi Foxguy,

I'm pleased your issue is Solved. Although it is a shame we don't know what caused it.

Originally Posted by foxguy

I couldn't find "Shrink to Fit" in 2003 or 2007, but I think autofit is just as good.

Here's a screenshot of the Format Cells dialogbox which can be brought up with [ctrl + 1] on a worksheet. I suspected that Autofit would be better for your purposes & after reading the Excel 2007 Help files for "Shrink to fit" my feelings are confirmed. I don't think I've ever used "Shrink to Fit", I just knew it existed & thought potentially it could relate to some odd cause for your error.

Rob

Hi Rob;

I never noticed that "Shrink to Fit" before.

I'm convinced that there was no error in the code. Just that the file got corrupted. The file also would not load properly if opened from a macro. If I opened it manually it worked fine. That problem disappeared after I recreated the file.