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.


Download Workbook

You can download the free practice Excel workbook from here.


Range.RowHeight Property in Excel

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

  • Syntax
Range.RowHeight

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.


6 Methods with VBA to Customize Row Height in Excel

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.

Dataset to customize row height 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.

VBA to Set Row Height in Excel

  • 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.

VBA Macro to Change Height of a Single Row in Excel

  • 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,

  • Worksheets(“Single Row”) -> Set the worksheet name.
  • Rows(8) -> The height for row 8 will be changed
  • RowHeight = 25 -> Set new row height

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 (7 Easy Ways)


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.

VBA Macro to Change Height of Multiple Rows in Excel

  • 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 (3 Quick Tricks)


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.

Increase the Existing Height of Row with VBA Macro in Excel

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

Read More: How to Increase Row Height in Excel (Top 4 Methods)


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.

Dataset of VBA to Autofit Row Height in Excel

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.

VBA to Autofit Row Height in Excel

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

Read More: How to Auto Adjust Row Height in Excel (3 Simple Ways)


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.

Dataset of VBA to Modify Row Height Based on Condition in Excel

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,

If Rows(i).RowHeight < 15 Then

Rows(i).RowHeight = 20

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

VBA to Modify Row Height Based on Condition in Excel

  • 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


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

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

2 Comments
  1. Any tips on double the height of all rows after an autofit? Or Autofit with some padding?

    • Hello, DJ!
      If you just Autofit all the selected rows you can use this code.

      Sub Autofit_Rows()
      Range(“A1:A10”).Select
      Selection.Rows.AutoFit
      Range(“A1”).Select
      End Sub

      After Autofit the rows, double the height of all rows is not possible actually. You can Autofit with some padding, please try this code. Hope this will help you.

      Sub AutoFitRows()
      Dim ws As Worksheet
      Dim rng As Range
      Application.ScreenUpdating = False
      For Each ws In ActiveWindow.SelectedSheets
      With ws.UsedRange
      .EntireRow.AutoFit
      For Each rng In .Rows
      rng.RowHeight = rng.RowHeight + 15
      Next rng
      .VerticalAlignment = xlCenter
      End With
      Next ws
      Application.ScreenUpdating = True
      End Sub

Leave a reply

ExcelDemy
Logo