|
|
|
|
|
|
|
|
|
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:
|
- Copy the above code.
- Open any workbook.
- Press Alt + F11 to open the Visual Basic Editor (VBE).
- From the Menu, choose Insert-Module.
- Paste the code into the right-hand code window.
- Close the VBE, save the file if desired.
|
|
Test the code:
|
- Tools-Macro-Macros, and double-click AutoFitAll.
|
|
Sample File:
|
AutoFitCols.zip 9.02KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 609 times.
|
|
|