Adjust vs Auto-Fit Column Width Excel VBA Macro

When working with Excel VBA, one of the most common formatting tasks is resizing columns so that the data is displayed properly. Excel offers two different approaches: setting a fixed column width, or automatically adjusting columns to fit their contents. In this post we see how to do both with VBA macros, and when each method is most useful.

Macro/VBA code:

  Sub AdjustColumnWidth()
 
      Dim ws As Worksheet
      Set ws = ActiveSheet
   
      'Set a fixed width for all used columns
      ws.UsedRange.Columns.ColumnWidth = 10
 
      'Automatically resize used columns
      ws.UsedRange.Columns.EntireColumn.AutoFit
   
  End Sub
 

Macro explained:

• First we create a Worksheet object variable (ws) and assig it to the active worksheet (or any other target sheet). This lets us reference the worksheet throughout the code using the variable instead of repeatedly writing ActiveSheet (despite in this case it can simply be omitted) or writing Sheets along the sheet name.
• Then we see how to adjust the column width by setting a fixed width of 10 points in the macro code example above. That adjusts the widht of used columns only - columns within the used range in that worksheet. The UsedRange property refers to the area of the worksheet that contains data. Therefore, only columns that belong to that area are affected.
• Alternatively, we could specify which columns we want to resize for example with: ws.Columns("A:C").ColumnWidth = 10. In that case, only columns A, B and C would be modified.
• The other option resizes the column width automatically. The AutoFit method overrides the fixed width assigned previously and adjusts each column so that the longest value fits the entire column and is fully visible.
• As before, the UsedRange property before Columns ensures only the columns that are part of the used range are adjusted. Alternatively, ws.Cells.EntireColumn.AutoFit would evaluate all columns in the worksheet, including columns outside the area containing data. 
• This approach is useful when the amount of data changes frequently and column widths need to adapt automatically to the contents.

This is how we adjust or auto-fit column width in Excel with VBA macros.

Other examples:

• How to Add a Multi-Select Drop Down in Excel with VBA macros.
• How to Get All Combinations From Array with Excel VBA macros
• How to Add Drop-Down List To Selection with Excel VBA macros