VBA to Customize Row Height in Excel (6 Methods)

Sometimes we need to modify rows when working on a large dataset in Excel for better readability. Implementing VBA is the most effective, quickest and safest method to run any operation in Excel. In this article, we will show you how to customize row height in Excel with the VBA macro.

Range.RowHeight Property in Excel

Excel’s RowHeight property specifies the height for all rows in a range of cells.

• Syntax

Here,

Range refers to the range of cells whose heights need to be customized.

• Return Value

Sets or modifies the height of the rows in the range specified.

Excel VBA to Customize Row Height: 6 Handy Methods

In this section, you will learn how to set new row heights, how to change row height for a single row and multiple rows, increase row height, autofit row height and modify row height based on conditions with VBA in Excel.

Above is the example dataset that this article will follow. In the dataset, the row height for all the rows is 15.

1. Embed VBA to Set Row Height in Excel

Let’s learn how to set a new row height for a specific row from our dataset. We will set a new row height for row number 7. The steps on how to do that are shown below.

Steps:

• Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• In the pop-up code window, from the menu bar, click Insert -> Module.

• Copy the following code and paste it into the code window.

Sub SetRowHeight()
    Rows("7:7").RowHeight = 30
End Sub

Your code is now ready to run.

This code will set 30 as the height of row 7.

• Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

As you can see in the picture below, row 7 is larger than the rest of the rows because its height is 30 now.

Read More: Row Height Units in Excel: How to Change?

2. VBA Macro to Change Height of a Single Row

If you want to change height only for a single row among your large dataset then follow the steps that we provide below. In our case, we will give you an example of how to do that with row 8 from our dataset.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Sub ChangeSingleRowHeight()
    Rows(8).RowHeight = 25
End Sub

Your code is now ready to run.

This code will change the previous row height from 15 to 25 for row number 8.

• Run this code and notice in the following picture that only the height for row 8 has changed into 25.

You can execute the above task in another way by providing the worksheet name inside the code. The code for that is:

Sub ChangeSingleRowHeightWS()
    With Worksheets("Single Row").Rows(8)
    .RowHeight = 25
    End With
End Sub

Here,

This is a very efficient way when you have so many worksheets in your Excel book and each of them required individual height customization.

Read More: How to Change the Row Height in Excel

3. Macro to Change Height of Multiple Rows in Excel

You can change the height of multiple rows with VBA. Let’s see how to update row height from 15 to 25 for our dataset.

Steps:

• As previously shown, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Sub ChangeMultipleRowsHeight()
    Rows("4:10").RowHeight = 25
End Sub

Your code is now ready to run.

This code will change the previous row height of 15 to a new height of 25 for row numbers 4 to 10 in the dataset.

• Run this piece of code and look in the image below that the height of our dataset is modified.

Read More: How to Copy Multiple Row Height in Excel

4. Increase the Existing Height of Row with VBA Macro

Suppose you don’t want to pass any numeric data inside the code while setting the row height, you just want to double the height than the existing height for a specific row. Below are the steps on how you can do that.

Steps:

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Sub IncreaseRowHeight()
    With Worksheets("Increase Height").Rows(7)
    .RowHeight = .RowHeight * 2
    End With
End Sub

Your code is now ready to run.

This code will double the height for row 7.

• Run this code and the height will be doubled for the row you need.

Read More: How to Increase Row Height in Excel

5. Macro Code to AutoFit Row Height in Excel

Notice in the image below. The height for row 7 is different from the others. We will AutoFit this row height with VBA.

Steps:

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Sub AutofitRowHeight()
    Rows(7).AutoFit
End Sub

Your code is now ready to run.

• Run this code and the row height for row number 7 will be AutoFit according to Excel’s fundamental row formation.

Read More: Excel VBA Autofit Row Height with Wrap Text

6. VBA to Modify Row Height Based on Condition in Excel

Consider the following dataset where all rows are consists of height 20 except rows 5, 7 and 9, they are quite smaller in height compared to the others.

We will learn how to modify their height and make it 20 with VBA macro.

Steps:

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• In the code window, copy the following code and paste it.

Sub RowHeightCondition()
    Dim i
    For i = 1 To 100
        If Rows(i).RowHeight < 15 Then
        Rows(i).RowHeight = 20
        End If
    Next i
End Sub

Your code is now ready to run.

Here,

These two lines mean that if any row height is less than 15 then set the new height of 20.

• Run this piece of code and the height for the rows that are below 15 will be set to 20.

Read More: How to Change & Restore Default Row Height in Excel

Download Workbook

You can download the free practice Excel workbook from here.

Conclusion

This article showed you how to customize row height in Excel with VBA. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.

Related Articles

• Auto Row Height Not Working in Excel
• How to Adjust Row Height to Fit Text in Excel
• How to Auto Adjust Row Height in Excel