Link to home
Start Free TrialLog in
Avatar of Andreas Hermle
Andreas HermleFlag for Germany

asked on

Autofit all columns of all worksheets using VBA

Dear Experts:

below code autofits all columns of the used range in the active worksheet. Could somebody tweak this code so that all worksheets get their columns auto-fitted with the exception of the worksheet named 'DataSource'

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Sub AutofitAllUsed()
 
Dim x As Integer
 
For x = 1 To ActiveSheet.UsedRange.Columns.Count
 
     Columns(x).EntireColumn.AutoFit
 
Next x
 
End Sub

Open in new window

Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Sub AutofitAllUsed()
 
Dim x As Integer
dim ws as worksheet

for each ws in worksheets
For x = 1 To ws.UsedRange.Columns.Count
 
     Columns(x).EntireColumn.AutoFit
 
Next x
next
End Sub

Open in new window

Sub AutofitAllUsed()
 
Dim x As Integer
Dim sht As Worksheet

    For Each sht In ActiveWorkbook.Sheets
        For x = 1 To sht.UsedRange.Columns.Count
         
             Columns(x).EntireColumn.AutoFit
         
        Next x
    Next sht
 
End Sub

Open in new window

Sub AutofitAllUsed()
 
Dim x As Integer
 
For x = 1 To ActiveSheet.UsedRange.Columns.Count
     If Cells(1, x).Value = "DataSource" Then
        'do nothing
     Else
     Columns(x).EntireColumn.AutoFit
     End If
Next x
 
End Sub
Sub AutofitAllUsed()
 
Dim x As Integer
Dim sht As Worksheet

    If sht.Name <> "DataSource" Then
        For Each sht In ActiveWorkbook.Sheets
            For x = 1 To sht.UsedRange.Columns.Count
             
                 Columns(x).EntireColumn.AutoFit
             
            Next x
        Next sht
    End If
    
End Sub

Open in new window

Sub AutofitAllUsed()
Dim x As Integer, ws As Worksheet

For Each ws In ActiveWorkbook.Sheets
    If ws.Name = "DataSource" Then
        'do nothing
        Else
        For x = 1 To ActiveSheet.UsedRange.Columns.Count
        Columns(x).EntireColumn.AutoFit
        
        Next
    End If

Next
End Sub

Open in new window

If one or more of the first columns on the sheet can be blank then you'll need this.
Sub AutofitAllUsed()
 
Dim x As Integer
Dim ws As Worksheet
Dim lngFirstCol As Long


For Each ws In Worksheets
    For lngFirstCol = 1 To 1000
        If Cells(1, lngFirstCol) <> Empty Then
            lngFirstCol = lngFirstCol - 1
            Exit For
        End If
    Next

    For x = lngFirstCol To ws.UsedRange.Columns.Count + lngFirstCol
        ws.Columns(x).EntireColumn.AutoFit
    Next x
Next
End Sub

Open in new window

correction


Sub AutofitAllUsed()
Dim x As Integer, ws As Worksheet

For Each ws In ActiveWorkbook.Sheets
    If ws.Name = "DataSource" Then
        'do nothing
        Else
        Sheets(ws.name).activate

        For x = 1 To ActiveSheet.UsedRange.Columns.Count

        Columns(x).EntireColumn.AutoFit
        
        Next
    End If
Next
End Sub

Open in new window

this will be a lot faster

Sub AutofitAllUsed()
Dim x As Integer, ws As Worksheet

For Each ws In ActiveWorkbook.Sheets
    If ws.Name = "DataSource" Then
        'do nothing
        Else
        Sheets(ws.name).activate
        Cells.Select
        Selection.Columns.AutoFit
    End If
Next
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Nice code byundt.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Andreas Hermle

ASKER

Dear Experts.

thank you very much for your overwhelming support. I really appreciate it.

I am afraid to tell you that byundt's code as well as Faruk's one are the only ones that work. All the codes that have the line ...

For x = 1 To sht.UsedRange.Columns.Count

Open in new window


or a similar expression integrated, do not work. These codes only work on the currently selected worksheet.

I was facing the same problem and therefore turned to this forum for help.

Anyhow thank you very much for your great help. I really appreciate it. Regards, Andreas

This is a great forum and great experts :-)
The problem with some of the other suggestions is not the statement For x = 1 To sht.UsedRange.Columns.Count but rather the following one--which needs to be qualified to the worksheet.
Columns(x).EntireColumn.AutoFit     'Only works on active worksheet. Should be sht.Columns(x).EntireColumn.AutoFit

Open in new window

Hi byundt, thank you very much for bringing this to my attention.