Column Width Problem

Jaamie

Board Regular
Joined
Apr 16, 2003
Messages
224
Office Version
  1. 2016
Platform
  1. Windows
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>
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Jaamie

Board Regular
Joined
Apr 16, 2003
Messages
224
Office Version
  1. 2016
Platform
  1. Windows
Some of the Columns have text and the use of Autofit will make these columns far to wide.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
So can't you AutoFit a Range that excludes those text entries?

Code:
Selection.Columns.AutoFit
 

Jaamie

Board Regular
Joined
Apr 16, 2003
Messages
224
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I can do that; but I am trying to find a solution for the ones that can. I am trying to determine if the screen density\font combination are impacting the column width I come up with in the above function. And if so, how can I account for this in my code.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
To do that you would need to know the algorithm that Excel uses to AutoFit. And I don't think that's readily available.

As an aside why are you using a number format that itself fits any column width?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,340
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Assuming the columns you want to resize contain constants only (no formulas), does this macro do what you want? As written, it resizes Columns A, D, E, F, and J to fit the largest number in their respective columns, but you can change this to the cells you want by modifying the blue highlighted text (note how single columns are specified though)...
Code:
Sub AutoFitNumbersCellsOnly()
  Dim Col As Range
  For Each Col In Range("[COLOR=#0000ff][B]A:A,D:F,J:J[/B][/COLOR]").Columns
    Col.SpecialCells(xlConstants, xlNumbers).Columns.AutoFit
  Next
End Sub
 

Jaamie

Board Regular
Joined
Apr 16, 2003
Messages
224
Office Version
  1. 2016
Platform
  1. Windows
Not sure what you are asking. I have 14 sheets with a financial report for each department. In an effort to keep the column widths as narrow little as possible, I initially set all column widths to 5. Then using the above function where FRow is the total row (the largest #'s) I use the actual number in the format it is displayed in and calculate the width. If it is bigger than 5, then I use it to set the width of that column, and so on.

Thank you for your interest.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,340
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Not sure what you are asking. I have 14 sheets with a financial report for each department. In an effort to keep the column widths as narrow little as possible, I initially set all column widths to 5. Then using the above function where FRow is the total row (the largest #'s) I use the actual number in the format it is displayed in and calculate the width. If it is bigger than 5, then I use it to set the width of that column, and so on.
If those Total rows contain formulas (which I assume they would give the name "total"), then you cannot use the code I posted in Message #7, but you should be able to use this modification to it to do what I think you want...

Code:
Sub AutoFitNumbersCellsOnly()
  Dim Col As Range
  For Each Col In Range("A:A,D:F,J:J").Columns
    Col.SpecialCells(xlFormulas, xlNumbers).Columns.AutoFit
  Next
End Sub

As a reminder from Message #7... as written, the macro resizes Columns A, D, E, F, and J to fit the largest number from any formulas in their respective columns, but you can change this to the cells you want by modifying the blue highlighted text (note how single columns are specified though)...
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,068
Perhaps something like this will work. There is the assumption that the Totals value is the maximum value in the column.
Code:
With oneColumn.EntireColumn
    With .Cells(.Rows.Count, 1)
        .Value = Format(Application.Max(.EntireColumn), "#,##.000")
        .EntireColumn.AutoFit
        .Delete shift:=xlUp
    End With
End With
 

Forum statistics

Threads
1,140,925
Messages
5,703,206
Members
421,281
Latest member
mfarhankhan87

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top