Andreas Hermle
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
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
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
Sub AutofitAllUsed()
Dim x As Integer
For x = 1 To ActiveSheet.UsedRange.Colu mns.Count
If Cells(1, x).Value = "DataSource" Then
'do nothing
Else
Columns(x).EntireColumn.Au toFit
End If
Next x
End Sub
Dim x As Integer
For x = 1 To ActiveSheet.UsedRange.Colu
If Cells(1, x).Value = "DataSource" Then
'do nothing
Else
Columns(x).EntireColumn.Au
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
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Nice code byundt.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ...
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 :-)
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
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.Coun t 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
ASKER
Hi byundt, thank you very much for bringing this to my attention.
Open in new window