Change Row Height and Column Width using Excel VBA

Description:

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. We will see with Examples.

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.

Examples

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:

1. Open an excel workbook
2. Press Alt+F11 to open VBA Editor
3. Insert a Module for Insert Menu
4. Copy the above code and Paste in the code window
5. Save the file as macro enabled workbook
6. Press F5 to execute itit

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 I am changing the Column B width to 25.

Sub sbChangeColumnWidth()

Columns("B").ColumnWidth = 25

End Sub

Examples

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:

1. Open an excel workbook
2. Press Alt+F11 to open VBA Editor
3. Insert a Module for Insert Menu
4. Copy the above code and Paste in the code window
5. Save the file as macro enabled workbook
6. 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.

Examples

Code to Auto Adjust Column Width

Following are the example to show you how to do this.

Sub sbAutoAdjustColumnWidth()

    Columns(2).AutoFit

End Sub

Code to Auto fit Row Height

Following are the example to show you how to do this.

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

Instructions:

Follow the instructions below to test the codes above.

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