Excel

AutoFit All Columns All Sheets at Once

Ease of Use

Intermediate

Version tested with

2000/XP 

Submitted by:

Zack Barresse

Description:

This macro autofits all columns in all sheets at the touch of a button or shortcut key. 

Discussion:

Often with the entering of data, I find myself constantly auto-fitting the columns. I put my cursor between A and B and double click (also: Format -> Columns -> Autofit Selection). This macro takes care of every sheet in the book all at once. When done, you're brought back to the sheet from which you began so you'll hardly notice something's occurred. 

Code:

instructions for use

			

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 Next wkBk Sheets(wkSt).Select Application.ScreenUpdating = True End Sub

How to use:

  1. Copy the above code.
  2. Open any workbook.
  3. Press Alt + F11 to open the Visual Basic Editor (VBE).
  4. From the Menu, choose Insert-Module.
  5. Paste the code into the right-hand code window.
  6. Close the VBE, save the file if desired.
 

Test the code:

  1. Tools-Macro-Macros, and double-click AutoFitAll.
 

Sample File:

AutoFitCols.zip 9.02KB 

Approved by mdmackillop


This entry has been viewed 608 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express