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

Users are saying about us...

    • SydneyKrukowski

      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>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube