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?
--
NC

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


"Nicola" wrote:

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?
--
NC

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

--
Rick (MVP - Excel)


"Nicola" wrote in message
...
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?
--
NC

Hi Nicola,
Autofit will not work if the cells are WrapText = True
First convert WrapText to false and then try again.
Alok

"Nicola" wrote:

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?
--
NC