Column AutoFit Sometimes Set Columns to 255 Width

[Hamsterdam]

Hi,

This my first post, apologies if I break any rules in the forum.

I am trying to use VBA to write a simple code to auto fit column width. I have data in columns A:Z and I have also merged cells D5:J5. Naturally, I think a simple line like the following will do the job:

Columns("A:Z").AutoFit

It does work in most cases... but roughly 1/10 times the Macro would set the column width to 255 (max) instead of auto fitting. I have googled around and tried the following but nothing worked:

ActiveSheet.StandardWidth = 17
Rows("1:100").AutoFit
With Columns("A:Z")

.ColumnWidth = 200 'set it to a wider width then let excel autofit narrow it​

.AutoFit​

End With

Additionally, it is extremely hard to replicate this problem. The Macro would work fine after several executions but would randomly set column width to 255 in one instance, so with each change it would take me 10-20 tries to confirm whether it actually solves the problem or not.

Any explanations or suggestions would be greatly appreciated.

 

[Joe4]

Welcome to the Board!

I have also merged cells D5:J5

I do not know if it is what is going on here, but I would strongly recommend NOT using merged cells. They cause nothing but problems in Excel!
And in the case here, where you are simply merging multiple columns across a single row, you can accomplish the same visual effect by using the "Center Across Selection" formatting option instead.
See here for details: https://www.atlaspm.com/toms-tutori...er-across-selection-instead-of-merging-cells/

See if that makes any difference with to your code.

 

[Hamsterdam]

And in the case here, where you are simply merging multiple columns across a single row, you can accomplish the same visual effect by using the "Center Across Selection" formatting option instead.

Thank you for the suggestion, this is way better than merging cells!

Sadly, it does not have any impact on the autofit issue. After a lot of trials I have found that autofit always failed to work on columns E:J. This is very confusing since columns D & K are basically the same as E:J, but autofit seems to work fine for columns D&K. The only difference I can find is that most of the value of cells in E:J is 0, where as values in D&K are in the range of 1,000~100,000,000. Besides that I have also made my macro did a bit of formatting to make things look better, but I doubt that is causing the problem.

Columns("D:K").NumberFormat = "_(* #,##0.00_);[Red]_(* (#,##0.00);_(* ""-""??_);_(@_)"
Range("A6:S6,D5:S5").Interior.Color = RGB(204, 255, 204)
Range("A8:S8").Interior.Color = RGB(153, 204, 255)
Range("E6:J6").Font.Color = RGB(255, 0, 0)

 

[Joe4]

You shouldn't need to first widen it in order to get Autofit to work.
What happens if you replace this:

[COLOR=#333333]With Columns("A:Z")[/COLOR]
[COLOR=#333333]    .ColumnWidth = 200 'set it to a wider width then let excel autofit narrow it[/COLOR]
[COLOR=#333333]    .AutoFit[/COLOR]
[COLOR=#333333]End With[/COLOR]

with this?

Columns("A:Z").EntireColumn.Autofit

 

[Hamsterdam]

Columns("A:Z").EntireColumn.Autofit

This is what I had in the beginning. The widening was added when google said adding that might solve my problem. It's kept in this post just to give a heads up on what I have tried so far.

 

[Joe4]

Are you sure that you don't have one cell in the column with a whole bunch of data, pushing it out to the 255?
Without seeing your sheet and what the data looks like, it is difficult to determine what the issue might be.

 

[Hamsterdam]

Are you sure that you don't have one cell in the column with a whole bunch of data, pushing it out to the 255?
Without seeing your sheet and what the data looks like, it is difficult to determine what the issue might be.

Yes, I am 100% sure because I have tried manually selecting columns and auto fit their width (which worked just fine). The mind boggling part is I am using the same set of data for testing purposes, yet somehow in 1 out of 10~20 runs of my Macro, it just seems to fail for no logical reason.

I have literally tried everything I could to solve this and probably spent too much time on such a minor issue. It's just frustrating that something as simple as autofit doesn't work 100% of the time for me. I guess the best alternative is to manually define the column widths:

    Columns("D:Z").EntireColumn.ColumnWidth = 17
    Columns("A:A").ColumnWidth = 9.8
    Columns("B:C").ColumnWidth = 42

 

[Joe4]

Is it the same columns every time that are the problem ones?
If so, what happens if you Autofit all the collumns at once in your code, and then after that Autofit that single column?

For example, if it was column C, then the code would look like:

Columns("A:Z").EntireColumn.Autofit
Columns("C:C").EntireColumn.Autofit

Just curious if that works...

 

[Hamsterdam]

Sorry I didn't get a chance to look at Macros until now.
Yes, the problem seems to persist for the same columns. This time, I have tried the following:

    Columns("A:Z").EntireColumn.AutoFit
    Columns("A:A").ColumnWidth = 9.8
    Columns("B:C").ColumnWidth = 42 'must fix the column width from columns A-C or the problem will happen
    Columns("D:Z").EntireColumn.AutoFit

and it seems to work properly (no weird column widths in 40 test runs). Then I tried to autofit twice, but that doesn't solve the issue.

    Columns("A:Z").EntireColumn.AutoFit
    Columns("A:Z").EntireColumn.AutoFit