Excel AutoFit Using View Code: Set It Up with VBA

VBA & macros · Updated November 20, 2026

To AutoFit columns automatically when you edit a worksheet, right-click that sheet’s tab, choose View Code, and add a Worksheet_Change VBA event that runs AutoFit on the edited columns. This uses Excel’s desktop VBA editor, so use Excel for Microsoft 365 on Windows and save the workbook as a macro-enabled file. The method adjusts column widths; it does not automatically adjust row heights.

What You’ll Need

  • The desktop version of Excel for Microsoft 365 on Windows. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros.
  • A workbook you are allowed to save as an Excel Macro-Enabled Workbook (.xlsm). If the file is currently .xlsx, save a separate .xlsm copy before adding code.
  • Permission to run macros. Your organization’s security policy may block them, and Excel may show a security warning when you open the file.
  • A copy of your workbook saved somewhere safe. VBA changes cannot be undone with Ctrl+Z, so keep a backup before running or changing code.

Step-by-Step

  1. Choose the worksheet that should AutoFit. This code belongs to one particular worksheet, not to the whole workbook. Open that sheet and decide whether fitting columns after every edit will suit its layout. It is useful for lists where users type entries of different lengths. It may be distracting on a carefully designed report where fixed column widths are important.
  2. Save a macro-enabled copy. In Excel, choose File > Save As, select a location, and choose Excel Macro-Enabled Workbook (*.xlsm) as the file type. Give the copy a clear name. If you save the code in an .xlsx file, Excel cannot retain the VBA project; it will warn you that macros cannot be saved in that format. Keep the original as a backup.
  3. Open the worksheet’s code window. At the bottom of the Excel window, right-click the tab for the sheet you selected and choose View Code. Excel opens the Visual Basic Editor directly at that worksheet’s code module. If the sheet tabs are not visible, first make sure the workbook window is not hiding them; you can also open the editor with Alt+F11 on Windows, then locate the workbook and its sheet under Project Explorer.
  4. Paste the event procedure into the correct module. In the View Code window, paste the complete code below into the large code pane. Do not paste it into a worksheet cell, a standard module, or the ThisWorkbook module. The worksheet module matters because Worksheet_Change runs in response to edits on that particular sheet.
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim changedColumns As Range
        Dim eventsWereEnabled As Boolean
    
        ' Remember the existing setting so Excel can restore it.
        eventsWereEnabled = Application.EnableEvents
        On Error GoTo CleanUp
        Application.EnableEvents = False
    
        ' Fit the columns containing the edited cells.
        Set changedColumns = Target.EntireColumn
        changedColumns.AutoFit
    
    CleanUp:
        Application.EnableEvents = eventsWereEnabled
    End Sub

    The procedure receives the cells that changed in Target, converts those cells to their entire columns, and applies AutoFit. A single-cell edit therefore fits one column; a paste spanning several columns fits each affected column. The event setting is temporarily disabled and then restored to its previous state so other event procedures do not get triggered unexpectedly.

  5. Check that the procedure is in the sheet module. Look at the editor’s title or Project Explorer to verify you are working in the intended sheet’s code window. If the code pane has dropdowns above it, the left dropdown may show General and the right one may show Worksheet_Change after the procedure is pasted. Excel recognizes the event by its exact name and parameters; do not rename it or add a second procedure with the same name to that sheet.
  6. Return to Excel and enable macros if prompted. Close or switch away from the Visual Basic Editor, then return to the worksheet. If Excel displays a security notice for a file you trust, use the available option to enable its content. Do not enable macros in an unfamiliar workbook simply to make the code run. The event is attached to the selected worksheet and will respond to edits there when macros are allowed.
  7. Test with a short and a long entry. Enter a brief value in an otherwise narrow column, then enter a longer value in another cell in that same column. After you commit each edit by pressing Enter or moving to another cell, the column should widen enough to show the longest content in it. To test a paste, copy a small block covering two columns and paste it onto the sheet; both edited columns should be fitted.
  8. Save and reopen the .xlsm file. Save the workbook after the code has been added. Close it and reopen the saved .xlsm copy to confirm the code remains present. If Excel asks about macros again, enable them only if you trust the file. If you share the workbook, tell recipients that its automatic behavior depends on their Excel macro settings.

Common Mistakes

  • Putting the code in the wrong place: A Worksheet_Change procedure pasted into a standard module will not run automatically as a sheet event. Use the View Code window for the particular sheet that should respond.
  • Saving as .xlsx: The .xlsx format does not preserve VBA code. Choose .xlsm and verify the file extension after saving.
  • Expecting the code to fit every column: This version fits only columns containing edited cells. It does not resize unrelated columns when you make a change elsewhere. To fit the whole sheet manually, select the sheet’s cells or columns and use Home > Format > AutoFit Column Width.
  • Expecting formulas to trigger the event when their results change: Worksheet_Change responds when a user or process changes cell contents. A formula recalculating because another cell changed does not itself trigger this event for the formula cell.
  • Expecting AutoFit to preserve a designed width: The code deliberately changes the edited columns’ widths. If you need a fixed-width layout, remove the event code or limit its action to a specific range.
  • Assuming merged cells will fit perfectly: Excel’s AutoFit does not reliably size columns or rows for merged-cell content. Consider unmerging those cells, using Wrap Text, or setting the dimensions manually.

Tips

Use this event on a data-entry sheet rather than on every sheet by default. A column’s width is based on the content and formatting Excel considers when it calculates AutoFit, so one very long entry can make a column much wider than the rest of the table. If you want to limit the behavior, add a check for the relevant input area before calling AutoFit. For example, a sheet whose user-entry area is A2:F500 could ignore changes outside that area; make sure any range check is written for the actual layout and tested with pasted data.

AutoFit and text wrapping serve different purposes. AutoFit Column Width makes a column wider to show its longest content on one line, while Wrap Text keeps the width more controlled and may increase row height. For long notes, a fixed-width column with wrapping can be easier to read than a very wide column. The code above does not set Wrap Text or fit row heights, so configure those separately if your layout requires them.

To fit widths once without VBA, select the columns or cells you want to adjust, then choose Home > Format > AutoFit Column Width. You can also double-click the boundary on the right side of a selected column heading to AutoFit that column. These manual options are useful for a one-time cleanup or when macros are unavailable. On Windows, Alt+F11 opens the Visual Basic Editor; on Mac, Option+F11 may open it, depending on the keyboard and system settings. Excel for Mac supports VBA, although its editor and security prompts may look different from Windows.

Troubleshooting

  • Nothing happens after editing: Confirm that the code is in the edited worksheet’s View Code module, the workbook is saved as .xlsm, and macros are enabled. Also check that Application.EnableEvents has not been left False by another macro. If necessary, open the Immediate window in the VBA editor and run Application.EnableEvents = True there.
  • The editor opens but you cannot find the sheet: In the Visual Basic Editor, show Project Explorer with Ctrl+R on Windows if it is hidden. Expand the workbook’s Microsoft Excel Objects folder and double-click the correct sheet. The sheet’s tab name and its VBA object name may differ, so check the sheet name shown in Excel.
  • The column does not get wider for a formula result: The event does not run just because a formula recalculates. Edit a cell in the formula’s dependency chain to see whether the event fits that edited column, or run AutoFit manually after recalculation. A Worksheet_Calculate event is a different approach; it can fire frequently, so adding AutoFit there may slow a workbook and should be designed carefully.
  • The width changes unexpectedly or the workbook feels slow: A large paste can cause several columns to be resized, and a long value can make one column unusually wide. Undo is not available for the VBA action with Ctrl+Z. Restore a suitable width manually or reopen the backup, and consider restricting the event to the columns where users enter data.
  • Excel reports an error or events stop working: Reopen the View Code window and confirm the procedure was pasted in full, including End Sub. The code restores the previous event setting when it reaches CleanUp, but a different macro that stops abruptly may leave events disabled. Save a copy before making repairs and ask your administrator if macro policy blocks the project.

Frequently Asked Questions

Does View Code mean the same thing as a macro?
View Code is a command that opens the VBA editor for a worksheet or workbook object. The code you add there is VBA; in this guide it is an event procedure that runs when the worksheet changes.

Will this AutoFit every worksheet in the workbook?
No. The code runs only on the sheet whose code module contains it. To apply the behavior to other sheets, add an appropriate event procedure to each sheet’s module, or use a workbook-level design that accounts for every sheet.

Can I use this in Excel for the web?
No. Excel for the web cannot create or run VBA event procedures. Open the .xlsm workbook in the desktop version of Excel to use the code; the web version can still display and edit workbook data, but the VBA automation will not run there.

Can I make it AutoFit rows instead of columns?
Yes, but the current code is specifically for column widths. Row height can be fitted with a different AutoFit operation, and wrapped text or merged cells can affect the result. Test a separate row-height procedure on a copy before relying on it.

Can I undo a width change with Ctrl+Z?
Usually not after a VBA event runs. Excel clears its normal undo history when VBA changes the workbook. Set the width manually, close without saving if appropriate, or restore a saved backup.

Will it run when I type in a cell and press Enter?
Yes, provided macros are enabled and the code is in that sheet’s module. Worksheet_Change runs after a cell edit is committed, and it also responds to pasted values in the affected range.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.