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

Boubekeur Merouane
May 19, 2019 at 11:05 pm

thank you !!!

Reply ↓
Brian
February 3, 2016 at 9:48 am

What does ColNo &lt
mean in the above code please.

Regards

Brian

Reply ↓
- SydneyKrukowski
  August 13, 2016 at 9:29 pm
  
  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
  
  Reply ↓
  - Longtooth
    December 21, 2016 at 10:32 am
    
    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.
    
    Reply ↓