Better AutoFit for Excel with VBA

After my annoyance that Excel couldn’t unhide multiple sheets finally got addressed last year, I discovered another area where Excel could use some improvement. This one concerns the AutoFit feature and can also be easily solved with a short VBA code. In my current job I started working with Excel files with considerable number of columns of various widths. This makes reading and navigating sheets cumbersome, as some columns are too narrow and hide important pieces of text in their cells, while others can be very wide so that users waste time scrolling horizontally to get from one place to the next. Theoretically, the Excel AutoFit feature should help, but this only fixes the first issue by making narrow columns wide enough to fit their entire contents, while at the same time exacerbating the second. What I would actually need is to run the AutoFit algorithm but restrict the maximum width to some value of my choosing, thus preventing columns from getting exceedingly wide.

After some experimentation and googling, I came up with the VBA code below. The MaxWd constant represents the maximum width of the columns I want after running the macro. You may change it to any other positive value you consider more appropriate. I found it a reasonable compromise to prevent columns with large strings of text from becoming too wide, and at the same time I can still comfortably glance their contents. For columns under this maximum width, I have added a function to round up their widths, just to give the contents a little bit more breathing room.

Sub Smart_AutoFit()
'
' Smart AutoFit Macro
' Autofits Excel columns, up to a maximum width of 21ch
'
' Maximum width of columns after Autofit
Const MaxWd As Integer = 21
Application.ScreenUpdating = False
Dim wd As Double
For Each Column In ActiveSheet.UsedRange.Columns
	Column.AutoFit
	wd = Column.ColumnWidth
	If wd > MaxWd Then
		Column.ColumnWidth = MaxWd
	Else
		Column.ColumnWidth = WorksheetFunction.RoundUp(wd, 0)
	End If
Next
Application.ScreenUpdating = True
End Sub

To have the macro available whenever Excel is open, you should store it in the Personal Macro Workbook, as described in this support article. That way you can also add a button to the Quick Access Toolbar and launch the macro with a single click. This macro has the added benefit of revealing any hidden columns containing data, so you can use it on files received from others that could contain hidden sections. The same approach could be used to autofit rows, but usually I found this to be a less common issue in Excel than columns.

There may be other, more fine-grained solutions to this. From what I gather, Excel AutoFit simply selects the maximum width of a cell in that column, based on the number of characters, so another possible procedure would be to loop through all the cells in a column, calculate the average of their widths (or some other measure such as median), and use that value as the optimal column width. But this method would take more time, especially in large datasets, and the added benefit would probably be marginal. In some cases, the result may even be further from what I set out to do: columns where most or all cells contain long texts will end up with large widths, defeating the purpose of making the sheet tighter for easier scrolling.