Set row height and column width in millimeters

The macros below lets you set row heights and column widths using millimeters as a scale:

Sub SetColumnWidthMM(ColNo As Long, mmWidth As Integer)
' changes the column width to mmWidth
Dim w As Single
    If ColNo < 1 Or ColNo > 255 Then Exit Sub
    Application.ScreenUpdating = False
    w = Application.CentimetersToPoints(mmWidth / 10)
    While Columns(ColNo + 1).Left - Columns(ColNo).Left - 0.1 > w
        Columns(ColNo).ColumnWidth = Columns(ColNo).ColumnWidth - 0.1
    Wend
    While Columns(ColNo + 1).Left - Columns(ColNo).Left + 0.1 < w
        Columns(ColNo).ColumnWidth = Columns(ColNo).ColumnWidth + 0.1
    Wend
End Sub

Sub SetRowHeightMM(RowNo As Long, mmHeight As Integer)
' changes the row height to mmHeight
    If RowNo < 1 Or RowNo > 65536 Then Exit Sub
    Rows(RowNo).RowHeight = Application.CentimetersToPoints(mmHeight / 10)
End Sub

This example macro shows how you can set the row height for row 3 and the column width for column C to 3.5 cm:

Sub ChangeWidthAndHeight()
    SetColumnWidthMM 3, 35
    SetRowHeightMM 3, 35
End Sub

Document last updated 1999-12-20 12:51:27 Printerfriendly version

User comments:

Ole P. from Norway wrote (2006-07-10 08:42:22 CET):

Re: Column widths & row heights
Like this:

For i = StartColNo to EndColNo
SetColumnWidthMM(i, 50)
Next i

Gilles B from Ontario Canada wrote (2006-07-09 01:51:51 CET):

Re: Column widths & row heights
How do we do it for multiple col or row at one time. Thanks

Ole P. from Norway wrote (2006-01-28 18:40:50 CET):

Re: Column widths & row heights
All you need to make the example above to work is included.
Application.CentimetersToPoints is a built-in function that converts a centimeter amount into the equvivalent in points.

Tim from Ontario, Canada wrote (2006-01-28 15:52:09 CET):

Column widths & row heights
I can't get this to work. I've never tried to write a macro before. There don't appear to be any conversion factors in the macro.
I don't think that the row heightw = Application.CentimetersToPoints is a problem - 1" = 72 points, therefore 1mm = 2.8346 points. The problem is with the column width.
Is there a sub rutine hidden in the line:
"w = Application.CentimetersToPoints"?

Sergiovery from Milan Italy wrote (2005-07-27 16:27:12 CET):

Site comment
I'm used to spend some minutes every day, looking on the web for Excel tooltips, examples and explanations to store and use later during development. This site is one of the most well arranged and clear-explaning I've found!
A lot of small components collected here, are now in my example-source-directory and many time I've fished there the right solution to "the problem of the moment". Thanks a lot to everybody!

Ole P. from Norway wrote (2004-11-17 00:29:22 CET):

Re: Conversion...I still don't understand!
Pixels is a short term for "picture element".
A pixel is a dot that represents the smallest graphical unit of measurement on a screen.
A pixel is screen-dependent, the dimensions of screen elements vary with the display system and resolution.
This makes it difficult to convert between e.g. millimeters and pixels without knowing the exact physical size of the pixel.

Points however is a fixed size that can be converted:
1 Inch = 72 Points, 1 cm = 28.3465 Points, 1 mm = 2.8346 Points

Ruan Brits from Cape town, Paarl, Pioneer Foods wrote (2004-11-16 12:16:12 CET):

Conversion...I still don't understand!
I want to know in simple terms, is it possible to convert pixels to milimeters and if so, how do i do it!!