Autofit -

  • Thread starter Thread starter Nicola
  • Start date Start date
N

Nicola

Hi

I have been trying to write a piece of code to expand cells horizontally to
accommodate text to save manually expanding cells.

The auto fit function doesnt work when trying to format the whole sheet.

Can anyone suggets what I need to write?
 
autofits all columns, then sets any column greater than 55 to 55:

With wks
.Cells.Columns.AutoFit
For Each objColumn In .UsedRange.Columns
If objColumn.ColumnWidth > 55 Then
objColumn.ColumnWidth = 55
End If
Next objColumn
End With
 
This will probably do what you want (just change the worksheet reference in
the For Each statement to your own worksheet's name); what it does is expand
only those columns where the entry does not fit and leaves all other columns
alone)...

Sub AutoFitWhenNecessary()
Dim R As Range
Dim LastColumn As Long
Dim CurrentWidth As Double
On Error GoTo Whoops
Application.ScreenUpdating = False
For Each R In Worksheets("Sheet1").Columns
CurrentWidth = R.ColumnWidth
R.AutoFit
If R.ColumnWidth < CurrentWidth Then
R.ColumnWidth = CurrentWidth
End If
Next
Whoops:
Application.ScreenUpdating = True
End Sub
 
Hi Nicola,
Autofit will not work if the cells are WrapText = True
First convert WrapText to false and then try again.
Alok
 
Back
Top