» Set row height and column width in millimeters using VBA in Microsoft Excel
VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions
CATEGORY - Cells, Ranges, Rows, and Columns in VBA
VERSION - All Microsoft Excel Versions
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
Book Store:
Recommended Books:
- The Fall of Advertising and the Rise of PR
- Analysis of Financial Statements
- Successful Business Planning in 30 Days: A Step-By-Step Guide for Writing a Business Plan and Starting Your Own Business
- VBA for Modelers: Developing Decision Support Systems Using Microsoft« Excel
- Financial Reporting and Analysis (2nd Edition)
- Advanced modelling in finance using Excel and VBA
No comments have been submitted.

