Macro to AutoFit columns and Rows in Excel

These VBA macros resize columns and rows to fit their contents: on the current sheet, on every sheet, or with a maximum width so long text doesn’t make a column huge.

Works inExcel 2010 and later, Windows and Mac
UndoNot possible. Save a copy first
Save asMacro-Enabled Workbook (.xlsm)

New to macros? See how to use the macros on this site: copy the code, paste it into a module with Alt + F11, and run it with Alt + F8.

AutoFit the active sheet

Sub AutoFitSheet()
    ActiveSheet.UsedRange.Columns.AutoFit
    ActiveSheet.UsedRange.Rows.AutoFit
End Sub

AutoFit every sheet

Sub AutoFitAllSheets()
    Dim ws As Worksheet
    For Each ws In Worksheets
        ws.UsedRange.Columns.AutoFit
        ws.UsedRange.Rows.AutoFit
    Next ws
End Sub

AutoFit with a maximum width

Sub AutoFitMaxWidth()
    Dim col As Range
    For Each col In ActiveSheet.UsedRange.Columns
        col.AutoFit
        If col.ColumnWidth > 50 Then col.ColumnWidth = 50
    Next col
End Sub

Columns wider than 50 characters are capped at 50. Turn on Wrap Text for those columns to see the full text.

Without a macro

Do this To
Double-click a column’s right border AutoFit that column
Alt, H, O, I AutoFit the selected columns
Alt, H, O, A AutoFit the selected rows

Things to know

  • AutoFit ignores merged cells.
  • Hidden rows and columns stay hidden.
  • Rows with Wrap Text turned on grow taller to fit the text.

Related macros