Autofit columns with a limit

A macro to the rescue

The column Autofit on the whole sheet is a great Excel feature. But if you have a few columns that have lots of text it can make using it problematic as you need to manually adjust those wide columns. Here’s a macro to make it easier.

The code below will apply column Autofit to the active sheet with a maximum column width of 35.

 

Sub AutoFitAllColumnsActive()
 
Dim c
Dim ws As Worksheet
Dim lnumCols As Long
Dim lMaxWidth As Long
 
Set ws = ActiveSheet
 
lnumCols = ws.UsedRange.Columns.Count
lMaxWidth = 35
 
ws.Columns.AutoFit
 
For c = 1 To lnumCols
  If ws.UsedRange.Columns(c).ColumnWidth > lMaxWidth Then
    ws.UsedRange.Columns(c).ColumnWidth = lMaxWidth
  End If
Next c
 
Set ws = Nothing
 
End Sub

 

If you need to do this flexibly via VBA code then below is a function that allows you to specify the sheet and the width you want to use.

 

Function AutoFitAllColumns(ws As Worksheet, lMaxWidth As Long)
Dim c
Dim lnumCols As Long
lnumCols = ws.UsedRange.Columns.Count
ws.Columns.AutoFit
 
For c = 1 To lnumCols
  If ws.UsedRange.Columns(c).ColumnWidth > lMaxWidth Then
    ws.UsedRange.Columns(c).ColumnWidth = lMaxWidth
  End If
Next c
 
End Function

 

To use the above function you would have code like

 

Sub Test()
 
AutoFitAllColumns Sheet1,40
 
End Sub

 

Please note: I reserve the right to delete comments that are offensive or off-topic.