top of page

Excel Macro to AutoFit Columns & Rows on Multiple Worksheets

Here's a useful little Excel macro that will allow you to autofit all of the rows and columns on multiple worksheets in an Excel spreadsheet.

See this example where we begin with a spreadsheet containing two worksheets that both have columns and rows which are not sized correctly.

Press ALT + F11 and go into Visual Basic. Right click on your workbook and select Insert . . . Module. Enter this macro in the new module:

Option Explicit Sub AutoFitAll() Application.ScreenUpdating = False Dim wkSt As String Dim wkBk As Worksheet wkSt = ActiveSheet.Name For Each wkBk In ActiveWorkbook.Worksheets On Error Resume Next wkBk.Activate Cells.EntireColumn.AutoFit Cells.EntireRow.AutoFit Next wkBk Sheets(wkSt).Select Application.ScreenUpdating = True End Sub

Close and return to Excel. Go to the View tab and select Macros . . . .View Macros. Run 'AutoFitAll'. The spreadsheet has now been automatically modified so the data in all cells is visible.

Thanks to Zack Barresse who posted this Macro at this site:

I modified the macro to include rows as well as columns.

bottom of page