Format Exact Column Width

K

klpc1

Cannot get my specific column width settings to apply. For example, trying
to change a column width from 12.75 to 12.73, but the change will not go into
effect. How can I make the column width exactly what I need?
 
G

Gord Dibben

You won't get Excel to give you that fine an adjustment.

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

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.

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 to exact sizes...........

Ole Erlandson has code for setting row and column dimensions.

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


Gord Dibben Excel MVP
 
K

klpc1

Thanks for your reponse.

Novice with codes and not too proficient in metric. Where/how do I use the
code you referenced to help with the width problem? Is there anything that
will convert the point/pixel measurements without going the metric route? I
have many excel files being imported into AutoCAD drawings, and the "end
result" total width measurement of all files needs to be the same, so they
all line up correctly on the drawings. Started with an Ariel font, then
tried using the proportionally-pitched Courier New, but end result is still a
little off.
 
G

Gord Dibben

There may be too many variables like resolution and different printer drivers
that make this quite difficult.

To see the correlation between pixels and widths, drag the column header and
wtch the ballon tip.

Did you browse through the "enlightening" discussion?

To use Ole's code, you would copy the three sets of code to a general module in
your workbook. Use the Sub ChangeWidthAndHeight() macro and change the
numbers to suit

To set 20 columns at a time to 50mm use this macro.

Sub Multi_cols()
Dim i as Long
For i = 1 to 20
SetColumnWidthMM(i, 50)
Next i
End Sub

To create graph paper as an example run this macro.

Sub ChangeWidthAndHeight()
Dim w As Long
Dim r As Long
For w = 1 To 40
SetColumnWidthMM w, 6.35
Next w
For r = 1 To 40
SetRowHeightMM r, 6.35
Next r
End Sub


Gord
 
G

Gord Dibben

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord

<snipped>
 
K

klpc1

Thanks, again, for your responses. I wil try these instructions, and let you
know how things worked out. Can't believe it would be so difficult to set a
specific column width in this application. Know what adjustments I need to
make. just can't make them happen.
 
Top