Setting Row Height and Column Width in Excel VBA

The following VBA procedure sets the height of specific rows and the width of specific columns:

Sub SetRowHeightAndColumnWidth()
    ' Activate the worksheet named "Sheet1"
    ThisWorkbook.Worksheets("Sheet1").Activate
    ' Set the height of rows 1 and 2 to 55 points
    Range("1:2").RowHeight = 55
    ' Set the width of columns B and D to 3 characters
    Range("B:B,D:D").ColumnWidth = 3
End Sub

Detailed Explanation:

This procedure adjusts the layout of the worksheet by modifying:

1. Row height for rows 1 and 2.
2. Column width for columns B and D.

RowHeight Property

The RowHeight property specifies the height of the row(s) in points.
In this example:

Range(« 1:2 »).RowHeight = 55

This sets the height of rows 1 and 2 to 55 points.

ColumnWidth Property

The ColumnWidth property determines the width of the column(s).
Column width is based on the number of characters of the default font that can fit in a cell.

In this case:

Range(« B:B,D:D »).ColumnWidth = 3

This sets the width of columns B and D to 3 characters, which is relatively narrow.

AutoFit Method: Adjusting Size Automatically

If you want Excel to automatically adjust the row height or column width based on the content, use the .AutoFit method.

Examples:

• To auto-adjust column G:

• Range(« G:G »).Columns.AutoFit

• To auto-adjust rows 1 and 2:

• Range(« 1:2 »).Rows.AutoFit

This is particularly useful when you’re not sure of the required size and want the dimensions to match the content exactly.

Summary

• RowHeight sets row height in points.
• ColumnWidth sets column width in character units.
• Use .AutoFit for automatic sizing based on content.