First off, my previously posted code erroneously Dim a LastColumn
variable... if you go with that routine, you can remove that declaration as
the LastColumn variable is not used in that code. However, using a
LastColumn approach to cut down on columns being processed, this code
appears to also work (and should be slightly more efficient than the first
code I posted)...

Sub AutoFitWhenNecessary()
Dim X As Long
Dim LastColumn As Long
Dim CurrentWidth As Double
With Worksheets("Sheet3")
LastColumn = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
On Error GoTo Whoops
Application.ScreenUpdating = False
For X = 1 To LastColumn
CurrentWidth = .Columns(X).ColumnWidth
.Columns(X).AutoFit
If .Columns(X).ColumnWidth < CurrentWidth Then
.Columns(X).ColumnWidth = CurrentWidth
End If
Next
End With
Whoops:
Application.ScreenUpdating = True
End Sub

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
This will probably do what you want (just change the worksheet reference
in the For Each statement to your own worksheet's name)...

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("Sheet3").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)


"Maury Markowitz" wrote in message
...
I have a large sheet that is created in VBA code running in Access. In
order to make the display look nice, when the numbers are finished
being pasted in I loop over the rows in the sheet looking for numbers
that are too large to fit and have been replaced by "####". It took me
a while to figure out how to do this (.text) but when I did I simply
used AutoFit and presto!

The problem is that the sheet is 50 columns wide and up to 3000 long.
Looping over the rows and looking in every column is eating up a
significant amount of time.

Is there some other way to find these things? Perhaps some way I can
examine a Region all at once? I see that .Find seems to work, but I
have hidden rows and .Find is unpredictable in these cases.

Maury