VBA to AutoFit Column Width

Excel has a default column width of 8.43 characters, corresponding to 64 pixels.

This means that if you enter data that is wider than the default column width, it will overflow to adjacent cells as depicted below:

example to autofit column width with VBA

In Excel VBA, you can use the ‘Range.AutoFit’ method to adjust column width and fit data so that it does not spill over adjacent columns.

The method adjusts the widths of the target columns to fit the widest content within each column, ensuring all the contents are visible.

In this tutorial, I will show you examples of how to use VBA to autofit column width in Excel.

Table of Contents

Example #1: VBA to AutoFit One Column

Suppose you have the data below in column A on Sheet1 that is wider than the default column width:

You can use the Excel VBA code below to adjust the width of column A to fit the entire contents in each cell:

Sub AutoFitOneColumn1()
ThisWorkbook.Worksheets("Sheet2").Range("A1").EntireColumn.AutoFit
End Sub

When you execute the code, it adjusts the width of the column A and fits the data so that it no longer overflows to adjacent columns:

Also read: How to Count Columns Using VBA

Example #2: VBA to AutoFit Multiple Adjacent/NonAdjacent Columns

Let’s say you have data in columns A and B on Sheet2 that are wider than the default column width:

You can use the Excel VBA code below to adjust the width of columns A and B and achieve the best fit for the data:

Sub AutoFitAdjacentColumns()
ThisWorkbook.Worksheets("Sheet2").Columns("A:B").EntireColumn.AutoFit
End Sub

When you run the subroutine, it adjusts the widths of columns A and B and fits the data:

Note: To AutoFit nonadjacent columns, let’s say, columns B, D, and F, separate the column references with commas.

For example, you can use the Excel VBA code below to AutoFit columns B, D, and F:

Sub AutoFitNonAdjacentColumns()
ThisWorkbook.Worksheets("Sheet4").Range("B:B,D:D,F:F").EntireColumn.AutoFit
End Sub

Example #3: VBA to AutoFit Visible Columns

Let’s say you have some hidden columns on Sheet3. You can use the Excel VBA code below to AutoFit only the visible columns and leave the hidden columns unaffected:

Sub AutoFitVisibleColumns()
ThisWorkbook.Worksheets("Sheet3").Cells.SpecialCells(xlCellTypeVisible).EntireColumn.AutoFit
End Sub

This code selects only the visible cells on the worksheet and then adjusts the width of the columns containing those cells to fit their entire contents.

Also read: VBA to Hide Rows Based On Cell Values

Example #4: VBA to AutoFit Visible Worksheet Columns in Active Workbook

You can use the VBA code below to AutoFit only the visible worksheet columns in the entire current workbook:

Sub AutoFitVisibleColumnsInWorkbook()
Dim sht As Worksheet
On Error Resume Next
   For Each sht In ThisWorkbook.Worksheets
     sht.Cells.SpecialCells(xlCellTypeVisible).EntireColumn.AutoFit
   Next sht
  On Error GoTo 0
End Sub

This code loops through each worksheet in the workbook, selects only the visible cells in each worksheet, and then adjusts the width of the columns containing those cells to fit their contents.

The error handling ensures that the code continues to execute even if errors occur during the autofitting process.

Example #5: VBA to AutoFit All Columns on a Worksheet

Suppose you have a large dataset with numerous columns on Sheet5. You can use the Excel VBA code below to AutoFit all columns on the worksheet:

Sub AutoFitAllColumns()
ThisWorkbook.Worksheets("Sheet5").Cells.EntireColumn.AutoFit
End Sub

Example #6: VBA to AutoFit All Worksheet Columns in Active Workbook

You can use the VBA code below to AutoFit all the worksheet columns in the entire current workbook:

Sub AutoFitAllColumnsInWorkbook()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Worksheets
    sht.Cells.EntireColumn.AutoFit
  Next sht
End Sub

This code iterates through each worksheet in the workbook and adjusts the width of all columns to fit their content.

Note: If there are any hidden columns in the workbook, the autofitting process will unhide them.

Example #7: VBA to AutoFit Columns in a Dynamically Changing Dataset

Suppose you have a dataset on the active sheet that changes dynamically, and you want to ensure that all columns display their content without truncation or unnecessary white space.

You can use the VBA code below that uses the ‘Range.CurrentRegion’ property to achieve this:

Sub AutoFitCurrentRegion()
ActiveSheet.Range("A1").CurrentRegion.Columns.AutoFit
End Sub

When you run this subroutine, the width of all columns in the rectangular block of data around cell A1 is adjusted to fit the content in those columns.

Note: This code only works on those cells that are contiguous with the active cell. Noncontiguous cells are unaffected even if they have data.

You can use the alternative code below that uses the ‘Worksheet.UsedRange’ property to AutoFit all cells on the active sheet with data:

Sub AutoFitUsedRange()
ActiveSheet.UsedRange.EntireColumn.AutoFit
End Sub

I showed you examples of Excel VBA to AutoFit Column width in this tutorial. I hope you found the tutorial helpful.

Other Excel VBA articles you may also like: