|
|
|
|
|
|
|
|
|
Excel
|
AutoFit sheet
|
|
|
Ease of Use
|
Easy
|
|
Version tested with
|
2002, 2003
|
|
Submitted by:
|
Zack Barresse
|
|
Description:
|
This is a quick, easy way to autofit the columns of the selected sheet; which will help for all cells with "######" showing (except those with negative time values).
|
|
Discussion:
|
Quite often I find myself double clicking between the column headings to AutoFit the columns, or selecting all columns and performing this task; especially as I do many calculations per day and Excel sheet real-estate is very, very expensive for me. This routine will do the activesheet AND will work with an array of sheets if selected. There is a check for an ActiveWorkbook so this can go well into an Excel add-in as-is.
|
|
Code:
|
instructions for use
|
Option Explicit
Public Sub AutoFitSheet()
If ActiveWorkbook Is Nothing Then Exit Sub
Dim i#
If ActiveWindow.SelectedSheets.Count > 1 Then
For i = 1 To ActiveWindow.SelectedSheets.Count
ActiveWindow.SelectedSheets(i).Cells.EntireColumn.AutoFit
Next
Else
Cells.EntireColumn.AutoFit
End If
End Sub
|
|
How to use:
|
- Copy above code.
- In Excel press Alt + F11 to enter the Visual Basic Editor (VBE).
- Press Ctrl + R to show the Project Explorer.
- Right-click desired file on left (in bold).
- Choose Insert -> Module.
- Paste code into the right pane.
- Press Alt + Q to close the VBE.
- Save workbook before any other changes.
- Call Function as either 1) a worksheet function, or 2) within a VBA routine.
|
|
Test the code:
|
- From an existing workbook, save first.
- Select any sheets. If you want to test the array, press shift, then select another sheet, then..
- Press Alt + F8.
- Choose 'AutoFitSheet'.
- Press 'Run'.
- Also, you can click the button on the sample workbook.
|
|
Sample File:
|
AutoFitSheetEx.zip 6.04KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 255 times.
|
|
|