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
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.
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,
- 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.
- 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.
- 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.
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: 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.
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.
- 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.
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