Column Width Problem

[Jaamie]

I am trying to set the column width (FCol) to accommodate the largest number in the Row (FRow). I can't use AutoFit.
Using the Function below, I set the width at 75% of the length of format of the value in the cell (A$). It works great on my machine but fails on others machines with different display units.

I am looking for a way that will work on any display.

Help, Thanks you, Jim.

<code>Function CheckWidth(FRow, FCol)
Columns(FCol).Select: K = Selection.ColumnWidth: ColPct = 0.75
Cells(FRow, FCol).Select: A$ = ActiveCell
B$ = Format(A$, "$* #,##0.00")
L = Int(Len(B$) + 1) * ColPct
If L > K Then
Columns(FCol).Select: Selection.ColumnWidth = L + 1
End If
End Function 'CheckWidth</code>

 

[Jaamie]

Thank you all for the help. I will try your suggestions. Also, another solution is for me to temporarily remove the text that prevents me from using Autofit.

BYW, I forget how to post Code. For the above I used <code> <\code> but it isn't right?

Thanks again, Jim

 

[Rick Rothstein]

Also, another solution is for me to temporarily remove the text that prevents me from using Autofit.<code>
</code>

That seems kind of drastic to me... try either my code (probably the one I posted in Message #9) or Mike's code, one or both of them should do what you want.

BYW, I forget how to post Code. For the above I used <code> <\code> but it isn't right?

The slash to use is a forward slash, not a backward one (and the slash is used only on the tag at the end, there is no slash on the tag at the beginning of the code).
</code>

 

[Rick Rothstein]

Perhaps something like this will work. There is the assumption that the Totals value is the maximum value in the column.

With oneColumn.EntireColumn
    With [COLOR=#000000].Cells([/COLOR][COLOR=#FF0000][B].Rows.Count[/B][/COLOR][COLOR=#000000], 1)[/COLOR]
        [COLOR=#FF0000][B].Value = [/B][/COLOR]Format(Application.Max(.EntireColumn), "#,##.000")
        .EntireColumn.AutoFit
        [COLOR=#FF0000][B].Delete shift:=xlUp[/B][/COLOR]
    End With[COLOR=#FF0000][/COLOR]
End With

Just occurred to me... I wondering if doing what you suggest risks having Excel setting the UsedRange to an incorrect value?

 

[mikerickson]

It might change the .Used Range temporarily.

I guess that I could use this that would only oversize Used Range by one row.

With oneColumn.oneColumn
    With .SpecialCells(xlCellTypeBlanks).Cells(1,1)
        .Value = Format(Application.Max(.EntireColumn), "#,##.000")
        .EntireColumn.AutoFit
        .ClearContents
    End With
End With

 

[Jaamie]

I tried this solution:
<code>
For Each Col In Range("D:Z").Columns
Col.SpecialCells(xlFormulas, xlNumbers).Columns.AutoFit
Next
</code>

It worked well except for columns with large negative numbers. Eg 156,567,23 worked, (135,234.45) did not.

I would like to try the second solution, however this code does not work by itself; I need to know how to use it.

With oneColumn.EntireColumn
With .Cells(.Rows.Count, 1)
.Value = Format(Application.Max(.EntireColumn), "#,##.000")
.EntireColumn.AutoFit
.Delete shift:=xlUp
End With
End With

Also, I tried to Post the code correctly but no luck. I am still doing something wrong. I entered:

less than sign-code-greater than sign
Line 1
Line 2
'
'
Less than sign-/code-greater than sign
It didn't print the html but it didn't create the Code: Box.

Thanks again,

Jim..

 

[Jaamie]

Figured out the last part about Posting Code. I was using a less than, grater than sign instead of [ and ]

 

[mikerickson]

You need to set the value of oneColumn to the column that you want adjusted.
If you want column C to be sized to its largest number.

Set oneColumn = Range("C:C")

With oneColumn.oneColumn
    With .SpecialCells(xlCellTypeBlanks).Cells(1,1)
        .Value = Format(Application.Max(.EntireColumn), "#,##.000")
        .EntireColumn.AutoFit
        .ClearContents
    End With
End With

The With section could be set up as its own sub with oneColumn as an argument.

 

[Jaamie]

Mike,

Thank you, I knew it was simple but simple is hard if you don't know where to start.

Jim

 

[Rick Rothstein]

Mike,

Thank you, I knew it was simple but simple is hard if you don't know where to start.

Just wondering if my proposed solution in Message #9 worked for you or not? It was setup to handle multiple columns, but basically for a single column, I was a reasonably fast one-liner...

Columns("C").SpecialCells(xlFormulas, xlNumbers).Columns.AutoFit

 

[Jaamie]

Rick,

In the end I used the following from your suggestion:

For Each Col In Range("P:Z").Columns
      Col.SpecialCells(xlConstants, xlNumbers).Columns.AutoFit
Next

This worked perfectly!! Thank you very much.

Jim..