Column Width Problem

J

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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Some of the Columns have text and the use of Autofit will make these columns far to wide.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Jaamie said:
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.
Click to expand...
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)...
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

X
  • Question
Locking/unlocking a cell with a Combo Box value Mouse click problem
Replies
0
Views
470
XrayLemi
X
S
  • Question
analyze and export based on multiple criteria
Replies
0
Views
328
slash32487
S
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
384
AndyEd
A
M
  • Question
VBA to escape to next set of code if no data were found
Replies
1
Views
609
cmowla
C
E
  • Question
Automatically populate cell after vba email sent
Replies
1
Views
681
eddieum1
E

Forum statistics

Threads
1,214,641
Messages
6,120,693
Members
448,979
Latest member
DET4492

Share this page

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
Back
Top