Set row height and column width in millimeters using VBA in Microsoft Excel

 

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


3 thoughts on “Set row height and column width in millimeters using VBA in Microsoft Excel

    • Looks like an html mixup.
      & l t ; (without spaces) = left angle bracket = “less than” symbol
      & g t ; (without spaces) = right angle bracket = “greater than” symbol

      • I’m not absolutely sure but Colnum &lt: 1 is equivalent to:
        Colnum Colnum > 255.
        The same applies to Rownum.

        So: if RowNo < 1 Or RowNo > 65536 Then ….
        Is: if RowNo 65536 Then ….

        I have no idea whether using < x or > is faster in execution but it’s certainly more code typing time than y. Maybe the guy’s keyboard is screwed up so “‘ aren’t working or modified to some other symbols.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>