Reputation: 303

VBA to select all columns in a worksheet and auto adjust all columns width in Excel 2010

I have a worksheet with with over 1000 columns. How can I select all columns in that sheet and auto adjust every column width. I tried

Sheets(1).Select
Selection.EntireColumn.AutoFit

But it didn't work. Any ideas?

Upvotes: 9

Answers (3)

Reputation: 21

This is a macro to autofit all the sheets in a workbook at once

Option Explicit
Sub Auto_Fit()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
sh.Columns.AutoFit
Next sh
End Sub

Upvotes: 1

Reputation: 131

You can use

Cells.Columns.Autofit

or, if you're not on the ActiveSheet then

Sheets(name_or_number).Columns.Autofit

Upvotes: 9

Reputation: 2356

Try this...

Sheets(1).UsedRange.Columns.AutoFit

Upvotes: 22