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:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=40

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

#autofitexcelrows #autofitexcelcolumns

Excel macro to unfreeze multiple worksheets

This macro will automatically unfreeze all of the worksheets on an Excel spreadsheet . . .

Sub UnFreeze()

'Update 20140317

Dim Ws As Worksheet

Application.ScreenUpdating = False

For Each Ws In Application.ActiveWorkbook.Worksheets

Ws.Activate

With Application.ActiveWindow

.FreezePanes = False

End With

Next

Application.ScreenUpdating = True

End Sub

. . . this can be an important tool when you're processing lots of Excel files.

The source is this site:

http://www.extendoffice.com/documents/excel/1516-excel-freeze-unfreeze-panes-multiple-sheets.html

Excel's Format Painter

Just a simple little Excel tip tonight. When you have a spreadsheet with varied formatting for different rows, and you enter a new column with a new formula, you may lose the formatting detail that you need.

1. See in Fig. 1 we have this spreadsheet where each team from the National League is 1900 in shaded a different color.

2. We insert a new column in order to calculate the total extra base hits, add in a simple addition formula (J2 +K2+L2), and then pull it down by pressing CTRL + D. The trouble is the shading of the top cell gets copied down too.

3. Select a column with the correct formatting and then go to the Home tab on the ribbon and click on 'Format Painter'.

4. Now click on the heading for the column that you want to add the formatting detail to and it will get painted to the colors you want.

#excel #formatpainter