Setting Column Widths

[Gleam]

I have a spreadsheet which has been created on another computer, and I am
having problems setting the column widths. My version of Excel 2003 is set to
use a default font size of 10. It appears that the computer the sheet was
created on, had a default font size of 12, because if I manually set the
column width to 10 and type in "1234567890" with a font size of 12 it fills
the column.

Having searched this forum I found a contribution from Nick HK and tried the
following code. On one of my spread sheets it worked fine - the message
returned 161.25, 161.25, 1.
When I ran it on the imported file it produced 207.75, 162.75, 1.27.
It has set the column width to 162.75 rather than the 161.25 I was expecting.
Is it possible to get it to set exactly? If not what margin of error can I
expect? In this example it is quite small but with some other code I was
getting 40.5 (aim) and 42 (actual) when using a factor derived from the
widest column (71.25) as the factor for a narrower column.

Sub test()
With Worksheets(1)
.Cells(5, 1).ColumnWidth = 30
Wpxl1 = .Columns(1).Width
factor = 161.25 / Wpxl1
.Cells(5, 1).ColumnWidth = 30 * factor
Wpxl2 = .Columns(1).Width
MsgBox Wpxl1 & ", " & Wpxl2 & ", " & Wpxl1 / Wpxl2
End With
End Sub

 

[JLGWhiz]

From the help file: One unit of column width is equal to the width of one
character in the Normal style. For proportional fonts, the width of the
character 0 (zero) is used.

You will not be able to set a precise column width. As you can see above,
it bases the width on the zero character while in the Normal style. As you
stated, XL2003 is Arial 10 for the Normal style. However, it seems to me
that if you select the entire sheet of the imported file and change the font
to Arial 10 the it should be compatible and allow you to set column widths as
you normally would.

 

[Gleam]

Thank you for the suggestion. From the help on changing the width of a single
column (2003): "The displayed column width is the average number of digits 0
through 9 of the standard font that fit in a cell."
Changing the font size in the entire sheet is not a welcome idea as many
cells have been set up with different font sizes. However I tried the idea
and it didn't work

 

[Gord Dibben]

Gleam

The number that appears in the Standard column width box is the average number
of digits 0-9 of the standard font that fit in a cell.

Row heights are measured in points or pixels. There are 72 points to an inch
and "maybe" 96 pixels to the inch.

For an interesting and enlightening discussion on this subject see

http://snipurl.com/dzz8

If you want to use VBA to set height and width in
mm(25.4mm/inch) for a more finite fit....................

Ole Erlandson has code for setting row and column dimensions.

http://www.erlandsendata.no/english/index.php?d=envbawssetrowcol


Gord Dibben Excel MVP

 

[Gleam]

Gord

Thank you for this. I have tried Ole Eraldson's code on two files and on one
columns B to R measure 173.5 mm and on the other 189 mm. Both have file /
pageset up / adjust set to 100% normal size. At the moment this is just one
rogue file amongst hundreds, so I hope no more turn up.