That'll work - 97% certain.
Make that 100% - but only for fields that render "#" when overflowed.
(i.e. Numeric and Date/Time).
Now, how about a plain old Text field?
e.g. .ColumnWidth = 5, but .Value = "Octagon Enhanced Loan Fund -
Wachovia".
The text renders OK as far as the column goes, but is truncated.
Any way to identify that situation?
Here's the code I have so far, which seems to deal with Numered/Date/
Time successfully:
---------------------------------------------------------
Sub ExpandColumns()
Dim curCell As Range
Dim lastRow As Long
Dim lastCol As Long
Dim lastCell As Long
Dim i As Long
Dim R As Long
Dim C As Long
Dim curWid As Double
Const incWid As Double = 0.1
Const maxWid As Long = 50
Application.ScreenUpdating = False
If WorksheetFunction.CountA(Cells) > 0 Then
lastCol = Cells.Find(What:="*", After:=[A1],
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
For R = 1 To lastRow
For C = 1 To lastCol
curWid = Columns(C).Width
Set curCell = Cells(R, C)
If Left(curCell.Text, 1) = "#" Then
Do Until Left(curCell.Text, 1) <> "#"
curWid = curWid + incWid
Columns(C).ColumnWidth = curWid / 10
Loop
End If
Next C
Next R
Application.ScreenUpdating = True
Set curCell = Nothing
End If
End Sub
---------------------------------------------------------