To autofit Excel cells with code, run VBA that applies AutoFit to the used range: use ws.UsedRange.Columns.AutoFit for column widths and ws.UsedRange.Rows.AutoFit for row heights. In Excel for Microsoft 365 on Windows, paste the macro below into a VBA module and run it; it sizes the used cells on the active worksheet to fit their contents.
What You’ll Need
- Excel for Microsoft 365 desktop on Windows, or the desktop version of Excel for Mac. Excel for the web does not run VBA macros.
- A workbook with the data you want to size. If the workbook contains important information, save a separate copy before running a macro: macro actions cannot be reversed with Ctrl+Z.
- Permission to run macros. If your organization blocks VBA, ask your administrator about approved options rather than changing security settings without permission.
Step-by-Step
- Open the workbook and select the right worksheet. Open the workbook in the Excel desktop app, then click the worksheet tab you want to format. The main macro below acts on the active sheet, so confirm the correct tab is selected before you run it. If you want to size every sheet in the workbook, see the variation in the Tips section.
- Save a safety copy. Use File > Save As to create a copy before running VBA, especially if you are new to macros. If you intend to keep the macro in the workbook, save it as an Excel Macro-Enabled Workbook (
.xlsm). Saving as.xlsxremoves VBA code when you close the file, although the formatting the macro has applied remains. - Show the Developer tab if it is hidden. In Excel for Windows, go to File > Options > Customize Ribbon, select the Developer check box in the Main Tabs list, and choose OK. On a Mac, open Excel > Settings (or Preferences in some releases) > Ribbon & Toolbar, then enable Developer. The names and placement can vary slightly between Mac releases.
- Open the Visual Basic Editor. On Windows, press Alt+F11. On a Mac, press Option+F11; some keyboards require Fn as well. You can also open the editor from the Developer tab by choosing Visual Basic. If the shortcut is intercepted by your keyboard or system, use the ribbon command instead.
- Insert a standard module. In the Visual Basic Editor, find your workbook in the Project Explorer. If that pane is not visible, use View > Project Explorer. Select the workbook project, then choose Insert > Module. A blank code pane appears. A standard module is a suitable place for a general-purpose macro, and it keeps the procedure separate from worksheet event code.
- Paste the macro into the module. Copy and paste this complete procedure into the blank code pane. It targets the active worksheet’s used range, autofitting both its columns and its rows.
Sub AutoFitActiveSheet()
Dim ws As Worksheet
Set ws = ActiveSheet
' Fit the used columns and rows on the active worksheet.
ws.UsedRange.Columns.AutoFit
ws.UsedRange.Rows.AutoFit
End Sub
- Run the code. Click inside the procedure in the Visual Basic Editor and press F5, or choose Run > Run Sub/UserForm. Alternatively, return to Excel, choose Developer > Macros, select
AutoFitActiveSheet, and choose Run. Excel may display a security notice; run the macro only if you trust the workbook and its source. - Check the result and save. Return to the worksheet and inspect the rows and columns. The width should accommodate the longest visible cell entry in each included column, and row height should expand when wrapped content needs more space. If the result is not what you intended, use the troubleshooting guidance below. Save the workbook after checking; the macro’s formatting changes are not undone with Ctrl+Z.
To autofit only certain columns: use a separate procedure and change the range to the columns you need. For example, this code adjusts columns A through F on the active sheet without changing row heights.
Sub AutoFitColumnsAtoF()
Dim ws As Worksheet
Set ws = ActiveSheet
' Fit only columns A through F.
ws.Columns("A:F").AutoFit
End Sub
To fit a specific row or group of rows instead, use the same pattern with a row reference: ws.Rows("2:20").AutoFit. Put that line inside a procedure after setting ws, and replace 2:20 with the rows you want. For example, the command ws.Rows("2:20").AutoFit changes heights for rows 2 through 20; it does not change column widths. Keeping the target narrow is helpful when only part of a large sheet should be reformatted.
Common Mistakes
- Running the macro on the wrong tab.
ActiveSheetmeans the worksheet currently selected when the code runs, not necessarily the sheet you were viewing when you pasted the code. Check the worksheet tab immediately before running it. For a fixed sheet, you can replaceSet ws = ActiveSheetwith a worksheet reference, such asSet ws = ThisWorkbook.Worksheets("Report"), provided a sheet named Report exists in the workbook containing the code. - Using the wrong range scope.
UsedRangemay include cells that look blank but have formatting or were previously used. That can cause extra columns or rows to be considered. If you know the actual data area, use a specific range, such asws.Range("A1:F40").Columns.AutoFit, instead. A fixed range is predictable but must be updated when the data grows. - Expecting AutoFit to solve every display issue. AutoFit sizes based on cell content, but it does not remove text, change the font, or make a narrow screen wider. Long text can create very wide columns. Consider wrapping text, using a sensible fixed width, or adjusting the layout when a full-width column is impractical.
- Overlooking merged cells. Excel’s AutoFit does not reliably calculate row height or column width for merged cells. If a heading spans merged cells, the macro may leave it clipped or oversized. For that area, unmerge the cells if appropriate, or set the row height or column width manually.
- Saving the macro in a format that discards it. The
.xlsxformat cannot retain VBA code. Use.xlsmif you want to keep and run the macro later. You can still use a macro to format a workbook and then save a separate non-macro copy if you no longer need the code.
Tips
Choose the narrowest target that meets your goal. If only columns A and B contain long labels and figures, fitting the entire used range can change widths in unrelated parts of the sheet. A targeted column range reduces surprises and makes the macro easier to reuse. Keep in mind that a column’s width applies to the whole worksheet column, not just the rows named in a selection.
For a workbook with a stable layout, replace ActiveSheet with a named worksheet reference so the macro always operates on the intended tab. Check the sheet name, including spaces and punctuation, and keep the reference inside the workbook that contains the macro when using ThisWorkbook. This is different from ActiveWorkbook, which means whichever workbook is currently active in Excel.
If you need to apply autofit to every worksheet, loop through the sheets rather than repeatedly running the active-sheet macro. This example processes worksheets in the workbook containing the code:
Sub AutoFitAllWorksheets()
Dim ws As Worksheet
' Fit each worksheet in the workbook that stores this macro.
For Each ws In ThisWorkbook.Worksheets
ws.UsedRange.Columns.AutoFit
ws.UsedRange.Rows.AutoFit
Next ws
End Sub
This affects every worksheet in ThisWorkbook, so review the workbook and save a copy first. It does not process chart sheets, which are not worksheets. For a workbook with many sheets or carefully designed layouts, test the code on a copy and consider fitting only the relevant columns on each tab.
Autofit is a formatting action, not a formula that recalculates whenever data changes. If entries are added or edited later, run the macro again. You can also run it from the Macros dialog, or assign it to a button if you are comfortable customizing the worksheet. Avoid placing code in worksheet events unless you specifically need automatic resizing after edits: event macros can run unexpectedly and may slow down a sheet with frequent changes.
Troubleshooting
- The Developer tab or Visual Basic Editor is unavailable: Confirm you are using the Excel desktop app. VBA macros cannot run in Excel for the web. On Windows, enable Developer under File > Options > Customize Ribbon; on Mac, check Excel > Settings or Preferences > Ribbon & Toolbar. A managed work device may restrict VBA, in which case contact your administrator.
- The macro does not appear in the Macros list: Check that the code is in a standard module in the intended workbook, that the procedure begins with
Suband ends withEnd Sub, and that it has no arguments. If the workbook was saved as.xlsxand closed, the code may have been removed. Save as.xlsmand paste the procedure again if needed. - Excel says macros are disabled: Only enable code from a source you trust. If Excel shows a security warning for your own file, follow your organization’s approved process. Do not lower macro security globally just to run an unfamiliar workbook.
- Some cells still look cut off: Check for merged cells, text wrapping, hidden rows or columns, and unusually long entries. For wrapped text, AutoFit the row after confirming Wrap Text is enabled. For merged headings, manually set the height or revise the merge. Also check that the text is not being visually obscured by a nearby object.
- The sheet becomes too wide or tall: The used range might extend beyond the visible data because of old formatting, or long entries may be driving the size. Undo is not available for macro actions, so close without saving only if you are certain you do not need other changes made since the last save. Otherwise, restore your saved copy or manually adjust the affected rows and columns.
Frequently Asked Questions
Can I autofit columns with VBA without changing row heights?
Yes. Use ws.UsedRange.Columns.AutoFit by itself, or specify columns such as ws.Columns("A:F").AutoFit. Leave out the Rows.AutoFit line.
How do I autofit row height with code?
Use ws.UsedRange.Rows.AutoFit to fit rows in the used range, or ws.Rows("2:20").AutoFit to fit rows 2 through 20. Set ws to the intended worksheet first.
Will this code work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in the Excel desktop app to run VBA. Excel for the web has other automation options in some Microsoft 365 plans, but those use Office Scripts rather than VBA.
Does autofit run automatically when I type new data?
No. The macro runs when you start it; later edits do not trigger it automatically. Run the procedure again when needed. Automatically running VBA after every edit requires event code and should be added only when that behavior is genuinely useful.
Can I run the same VBA on a Mac?
Excel for Mac desktop supports VBA, although keyboard shortcuts and some settings may differ. Use Option+F11 to open the editor on many Mac keyboards, or open it from the Developer tab. Excel for the web does not run VBA on either platform.
Why does a merged heading remain clipped after autofitting?
Excel does not reliably autofit merged cells. Set the relevant row height or column width manually, or change the layout to avoid merging if that suits the worksheet. Check wrapped text as well, since row height may need to be fitted after wrapping is enabled.
Related
- Freeze a Specific Row in Excel: Steps, Limits, and Tips
- Freeze Panes in Excel: Keep Rows and Columns Visible
- Freeze in Excel: Keep Rows and Columns Visible
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Excel Get Range Address: Examples and Common UsesIn Excel VBA, get a range’s address with its Address property: for example, Range("B2:D5").Address returns $B$2:$D$5 by default.…
- Excel VBA Freeze Panes at a Specific CellYes. Activate the worksheet, select the cell that marks the top-left corner of the scrolling area, then set…
- Delete the Last Row in Excel VBA SafelyTo delete the last data row of an Excel table, use the table’s ListRows collection: tbl.ListRows(tbl.ListRows.Count).Delete. For a…
- Excel VBA INDEX: Return Values from a RangeIn Excel VBA, “INDEX” usually means Excel’s INDEX worksheet function, called as Application.WorksheetFunction.Index(...) or Application.Index(...) to return an…
- Vba loop through rows?Learn how to loop through Excel rows with VBA, find the last row, handle conditions and deletions, and…
- Left in Excel VBA: Syntax, Examples, and Common IssuesIn Excel VBA, Left returns a specified number of characters from the beginning of a string. For example,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.