Autofitting columns with a condition

[chamster]

I would like to change the width of my columns in such a way that the size gets an adapted span to whatever might be in the cells BUT i wish the size never to be smaller than a certain amount of pixels (centimeters, cows feet, whatever Excel uses). How can i do that?

[yogi_bear_79]

This snippet autofits each column in the worksheet, then goes back and ensures that columns L:W are not less than 5, and then forces columns X:Y to be a certain size.


If ActiveWindow.SelectedSheets.Count > 1 Then
For i = 1 To ActiveWindow.SelectedSheets.Count
ActiveWindow.SelectedSheets(i).Cells.EntireColumn.AutoFit
Next
Else
Cells.EntireColumn.AutoFit

End If
For Each Col In Columns("L:W")
If Col.ColumnWidth < 3.71 Then Col.ColumnWidth = 3.71
Next Col
Columns("X:X").ColumnWidth = 5
Columns("Y:Y").ColumnWidth = 29.71

[chamster]

This snippet autofits each column in the worksheet, then goes back and ensures that columns L:W are not less than 5, and then forces columns X:Y to be a certain size.


If ActiveWindow.SelectedSheets.Count > 1 Then
For i = 1 To ActiveWindow.SelectedSheets.Count
ActiveWindow.SelectedSheets(i).Cells.EntireColumn.AutoFit
Next
Else
Cells.EntireColumn.AutoFit

End If
For Each Col In Columns("L:W")
If Col.ColumnWidth < 3.71 Then Col.ColumnWidth = 3.71
Next Col
Columns("X:X").ColumnWidth = 5
Columns("Y:Y").ColumnWidth = 29.71


Thanks. Very helpful.