How to Change Row Height/Column Width using VBA in Excel

In this tutorial, you will explore the ways to write a VBA code to change the height of the row and width of the column.

You will also see how you can get the value of height and width and store it in value and then you can increase it by a certain percentage.

Change Row Height using VBA

To change the height of the row, you need to use the entire row property and then the row height property. In the row height, you need to specify the height (as a number) that you want to apply. You can also use an input box or use a cell value to enter the height.

  1. Specify the row for which you wish to change the height.
  2. Type and dot (.) and then select the entire row property.
  3. Again, type a dot and select the row height property.
  4. Use an equal sign to specify the row height.

Helpful Links: Run a MacroMacro RecorderVisual Basic EditorPersonal Macro Workbook

Get the Row Height and then Change it

Sometimes you need to change the row height to some extent instead of using a number to define it. In that case, you can get the row height from the row, store it in a variable, and then change it. Consider the following code.

Dim iRow As Long
iRow = Rows(1).EntireRow.RowHeight
Rows(1).EntireRow.RowHeight = iRow * 1.5

When you run the above code, it takes the row height from row 1, stores it to the variable iRow, in the end, increases it by 50%.

Row Height to AutoFit

You can also change the height of the row to the content therein. And if there’s no content in the cells of the row, the above code will change the row height to the default.

Rows(1).EntireRow.AutoFit

Here’s a quick tutorial about using autofit in VBA.

Change Column Width using VBA

To change the width of the column, you need to use the entire column property and then the column width property.

In the column width, you need to specify the width (as a number) that you want to apply. You can also use an input box or use a cell value to enter the width.

Columns(1).EntireColumn.ColumnWidth = 20
  1. Specify the column for which you wish to change the width.
  2. Type and dot (.) and then select the entire column property.
  3. Again, type a dot and select the row width property.
  4. Use an equal sign to specify the column width.

Get the Column Width and then Change it

Dim iColumn As Long
iColumn = Columns(1).EntireColumn.ColumnWidth
Rows(1).EntireColumn.ColumnWidth = iColumn * 1.5