Change Row Height, Column Width VBA (Autofit)

Today we will learn to change the row height and column width using VBA in excel. We will also learn to automatically autofit the rows and columns once we enter data in the spreadsheet. So let us start learning.

Set Row Height and Column Width

We can use the Rows and Columns method in VBA to refer to rows or columns and then use the corresponding properties to change the dimensions. For instance, we use the following properties for changing column width and row height respectively.

• RowHeight – This property is used with reference to a single row or multiple rows to change its height. We can use the Rows function to refer to rows in VBA.
• ColumnWidth – This property is used with reference to a single or multiple columns to change the width. We can refer to columns in the spreadsheet with the help of the Columns Function in VBA.

Example1 – Set Row Height for Single and Multiple Rows

In this example, we would change the height of row 3 in the active worksheet to 30. Use the following Sub Procedure. Place it in a new module and then run the Sub procedure to see the results.

Sub exChangeRow3Height()
'change the row height of third row to 30
Rows(3).RowHeight = 30
End Sub

As a result, the 3rd-row height changed to 30.

We can do this for multiple rows by specifying the top row and the bottom row number with a colon ( : ) symbol in the Rows Function. Let us say we wish to change the row height of rows from 2 to 10, then use this Sub Procedure.

Sub exChangeRow1to10Height()
'change the row height of third row to 30
Rows("1:10").RowHeight = 30
End Sub

Run this Sub procedure and it will change the height of all rows between row 1 and row 10.

Example 2 – Changing Column Width

In this example, we will learn to change the width of a column using the ColumnWidth property of the Columns Function. The columns function will refer to columns whose width we wish to change. We will take an example of noncontiguous columns.

Let us say we wish to change the width of Columns A, C, D, and F then we may use the following Sub Procedure.

Sub exChangeColumnACDFwidth()
'change width of discrete columns
Dim myCol As Range
'iterate from column A to Column F
For Each myCol In Columns("A:F")
'check if we have desired column number , A=1, C=3, D=4 and F=6
If (myCol.Column = 1 Or myCol.Column = 3 Or myCol.Column = 4 Or myCol.Column = 6) Then
    'change the column width
    myCol.ColumnWidth = 20
End If
Next myCol
End Sub

Here we have used For Each loop to iterate from columns 1 to 6 and check if the current column is the one whose width we wish to change.

When we run this Sub Procedure, the width of columns A, C, D, and F changes to 20 as follows.

Obtain Column Width and Row Height

We can get the already defined value of column width or row height using the property.

We can use this line of code to display the width of column A using a message box.

MsgBox Columns("A").ColumnWidth

Similarly, we get the height of row 1 using this line of code.

MsgBox Rows("1").RowHeight

AutoFit the Column Width using the Cell Address

The AutoFit property lets us resize the column width based on the length of data the cell contains. The entire column would resize itself based on the length of content in one cell.

Let us say we wish to alter the width of column A based on the length of the content of cell A1.

We can use this Sub Procedure.

Sub autoFitColumnA()
'resize column A based on content of cell A1
Range("A1").EntireColumn.AutoFit
End Sub

AutoFit the Column Width once Data is Entered in the Cells

This method would work as follows.

This code autofits the column width each time data is entered in the cells. We can use the Selection Change worksheet event for this. Follow these steps to do the same.

• Go to the Worksheet for which you wish to apply to autofit feature in the VBA project. Open the code window for Sheet1 and choose Worksheet and SelectionChange event.

• Enter the following code in the SelectionChange event of the Worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.EntireColumn.AutoFit
End Sub

• Close the VBE and enter data in Sheet1 to see this functionality working.

This brings us to of changing row height and column width using VBA.

Thank you for reading.