Excel VBA – Change Row Height and Column Width

Some times we may enter the data into cells more than it’s width. In this case we can not able to see entire text. So we can change row height and Column width using excel using VBA. So that we can see entire data in that cell. When you have more lengthy data in cells, you can Auto Adjust Column Width or Row Height in Excel VBA to show the entire data. So that users can see the entire data in the cells.

Changing Row Height in Excel VBA

We can change row height in Excel using RowHeight Property of a Row in VBA. See the following example to do it. The following example will change the height of the 3rd Row to 25.

Sub sbChangeRowHeight()

'Changing the 3rd row Height
Rows(3).RowHeight = 25

End Sub

We can also set the height for multiple rows, the following example will change the height of the 3rd to 20th row height to 25.

Sub sbChangeRowHeightMulti()

'Changing the 3rd-25the row Height
Rows("3:25").RowHeight = 25

End Sub

Instructions

• Open an excel workbook
• Press Alt + F11 to open VBA Editor.
• Insert a Module for Insert Menu.
• Copy the above code and Paste in the code window.
• Save the file as macro enabled workbook.
• Press F5 to execute it.

Changing Column Width in Excel VBA

We can change column width in Excel using ColumnWidth Property of a Column in VBA. See the following example to do it. In this example we are changing the Column B width to 25.

Sub sbChangeColumnWidth()
Columns("B").ColumnWidth = 25
End Sub

We can also set the column width for multiple columns at a time, see this Example I am changing the Column B to E width to 25.

Sub sbChangeColumnWidthMulti()
Columns("B:E").ColumnWidth = 25
End Sub

Instructions

• Open an excel workbook.
• Press Alt + F11 to open VBA Editor.
• Insert a Module for Insert Menu.
• Copy the above code and paste in the code window.
• Save the file as macro enabled workbook.
• Press F5 to execute it.

Auto Adjust Column Width and Row Height using Excel VBA

We can use AutoFit method of Columns and Rows in Excel using VBA to Auto Adjust the rows and Columns. Following are the example to show you how to adjust a column:

Sub sbAutoAdjustColumnWidth()
    Columns(2).AutoFit
End Sub

Following are the example to show you how to adjust a row:

Sub sbAutoAdjustRowHight()
   Rows(2).AutoFit
End Sub

Instructions

Follow the instructions below to test the codes above:

• Open an excel workbook.
• Press Alt + F11 to open VBA Editor.
• Insert a Module for Insert Menu.
• Copy the above code and paste in the code window.
• Save the file as macro enabled workbook.
• Press F5 to execute it.