Method 1 – Using the Excel Sort Tool to Add a Space Between Rows
Assuming we have a dataset (B4:D9) of the employee names and their total working week along with the working hours per week, we are going to add a space between the rows.
STEPS:
- We need a helping column (E5:E9) adjacent to the dataset.
- Fill a series of numbers (1,2,…..,5) in that range (E5:E9).
- Select the range (E5:E9), copy it, and paste it at the bottom of the last cell.
- Select all the series numbers.
- Go to the Data tab.
- Click on the A to Z sorting option from the Sort & Filter section. Here, a small box pops up.
- Select ‘Expand the selection’.
- Click on the Sort.
- We can see that the spaces are added between the rows.
- We can delete the helping column with the series of numbers and make the dataset suitable for use.
Method 2 Using an Excel VBA Code to Insert a Space Between Rows
We have a dataset (B4:D9) of employee names with their working weeks and hours.
STEPS:
- Select the worksheet from the sheet tab.
- Right-click on it.
- Select View Code.
- A Microsoft Visual Basic for Application module pops up.
- Press the Alt+F11.
- Enter the following code:
Option Explicit
Sub AddSpaceBetweenRows()
Dim rng As Range
Dim i As Long
Set rng = Range("B5:E9")
For i = rng.Rows.Count To 2 Step -1
rng.Rows(i).EntireRow.Insert
Next i
End Sub- Select the Run option or press F5.
- Go to the worksheet and see the added space between rows.
Read More: How to Space Rows Evenly in Excel
Download the Practice Workbook
Download the following workbook to practice.
Related Articles
- Add Space between Numbers in Excel
- How to Add Space Between Text in Excel Cell
- How to Add Blank Space Using Excel Formula
- How to Insert Tab in Excel Cell
- How to Space Columns Evenly in Excel
- How to Space out Cells in Excel
<< Go Back to Space in Excel | Text Formatting | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hi Nuraida,
Love the tutorial! I’m intending on applying the code to a workbook with multiple sheets, any tips on how to do this? When I make the edits it runs the code on the first sheet but not the 2nd, 3rd, 4th etc.,
Thanks again!
Froggy
Hi Froggy,
Hope you are doing well. Thanks for reaching out to us with your issue.
As you intend to apply the above code to a workbook with multiple sheets so you need to add some extra lines in your code to define all available worksheets or selected worksheets.
Here, I will give you two solution
1. For selected sheets
2. For all the available sheets in a workbook
1. If you want to apply the same code in some selected sheets then use the code given below where I declared sheets names by using an Array.
Option Explicit
Sub AddSpaceBetweenRows()
Dim all_sheets As Worksheet
Dim rng As Range
Dim i As Long
Dim all As Variant
Set all = sheets(Array(“VBA1”, “VBA2”))
For Each all_sheets In all
all_sheets.Select
Set rng = Range(“B5:E9”)
For i = rng.Rows.Count To 2 Step -1
rng.Rows(i).EntireRow.Insert
Next i
Next
End Sub
2. To loop through all the available sheets of your workbook you will need to use the code given below where I declared all_sheets and used an extra For Loop.
For your better understanding, I’m providing the modified code here,
Option Explicit
Sub AddSpaceBetweenRows()
Dim all_sheets As Worksheet
Dim rng As Range
Dim i As Long
For Each all_sheets In Worksheets
all_sheets.Select
Set rng = Range(“B5:E9”)
For i = rng.Rows.Count To 2 Step -1
rng.Rows(i).EntireRow.Insert
Next i
Next
End Sub
Note: Based on your Excel file you will need to change the sheet name and the cell range selection.
For further queries comment down below.