Autofit columns

[Rainmanne]

I need to auto fit the visible columns with the data after the data are imported. I tried to use this:

ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Columns.AutoFit

and this

ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).EntireColumn.AutoFit

But what the code does is makes all the visible columns with data standard size rather auto fit them to the widest cell. I wonder what I am doing wrong?

 

[Joe4]

This works just fine for me:

ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Columns.AutoFit

Are you sure that you don't have the "Wrap text" format set for these columns?
That would prevent it from working the way you want. You want that option to be unchecked.

You could ensure that in your VBA code like this:

ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Columns.WrapText = False
ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Columns.AutoFit

 

[Rainmanne]

This works just fine for me:


Are you sure that you don't have the "Wrap text" format set for these columns?
That would prevent it from working the way you want. You want that option to be unchecked.

View attachment 96047

You could ensure that in your VBA code like this:

ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Columns.WrapText = False
ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Columns.AutoFit

Thanks a lot for the tip about the WrapText! It was not that but I will now use it just in case.
The problem as I realised was that I use code optimisation:

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
ActiveSheet.DisplayPageBreaks = False
Application.DisplayAlerts = False

And it prevented AutoFit working properly. I have now moved AutoFit after stopping the optimisation:

ActiveSheet.DisplayPageBreaks = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

and it works properly now.

 

[Joe4]

Or maybe you just need to turn ScreenUpdating on back at the end of your code!

Application.ScreenUpdating = True

 

[Rainmanne]

Or maybe you just need to turn ScreenUpdating on back at the end of your code!

Application.ScreenUpdating = True

No, no, it's turned on but at the very end of the code

 

[Joe4]

No, no, it's turned on but at the very end of the code

That is where is should be.

Just make sure that your don't have anything that would exit the code before that point (and never run those lines).

I really don't think any of those other lines of code you posted should interfere with AutoFit.
You may have other things at play, but since you have not posted the entire code block, I can only guess.
But if moving the code around gets it work, then I guess you can go with that.