How to insert specific number of rows at fixed intervals in Excel?
In an Excel worksheet, you can manually insert blank rows between existing rows by using the built-in Insert function. However, when dealing with a large set of data or when you need to regularly insert, for example, two blank rows after every third or nth row, performing this task manually becomes time-consuming and inefficient. Automating the insertion of blank rows at fixed intervals not only saves time but also minimizes errors, making your workflow more streamlined. The following methods will show you how to quickly and conveniently accomplish this task, including solutions for inserting blank rows based on certain criteria or repeating rows, both with and without VBA.
➤ Insert specific number of blank rows into data range at fixed intervals with VBA code
➤ Insert specific number of blank rows into data range based on cell values with VBA code
➤ Insert specific number of blank rows into data range at fixed intervals with a handy feature
➤ Insert specific number of blank rows at fixed intervals with Excel formulas
➤ Copy and insert rows multiple times based on specific numbers with VBA code
➤ Copy and insert rows multiple times based on specific numbers with an amazing feature
Insert specific number of blank rows into data range at fixed intervals with VBA code
The following VBA code can help you automatically insert a specific number of blank rows after every nth row within your existing data range. This approach is suited for users comfortable with macros and who need to handle medium to large datasets efficiently. Please perform the following steps for this solution:
1. Hold down the ALT + F11 keys in Excel, which opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Insert specific number of rows into data at fixed intervals
Sub InsertRowsAtIntervals()
'Updateby Extendoffice
Dim Rng As Range
Dim xInterval As Integer
Dim xRows As Integer
Dim xRowsCount As Integer
Dim xNum1 As Integer
Dim xNum2 As Integer
Dim WorkRng As Range
Dim xWs As Worksheet
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xRowsCount = WorkRng.Rows.Count
xInterval = Application.InputBox("Enter row interval. ", xTitleId, 1, Type:=1)
xRows = Application.InputBox("How many rows to insert at each interval? ", xTitleId, 1, Type:=1)
xNum1 = WorkRng.Row + xInterval
xNum2 = xRows + xInterval
Set xWs = WorkRng.Parent
For i = 1 To Int(xRowsCount / xInterval)
xWs.Range(xWs.Cells(xNum1, WorkRng.Column), xWs.Cells(xNum1 + xRows - 1, WorkRng.Column)).Select
Application.Selection.EntireRow.Insert
xNum1 = xNum1 + xNum2
Next
End Sub
3. After pasting this code, press F5 to run it. A prompt box will pop up, reminding you to select the data range where you want to insert blank rows, as shown below:
4. Click OK. Another prompt box appears, where you should enter the desired row interval—for example, entering3 will insert blank rows after every third row:
5. Continue by clicking OK, then enter the number of blank rows you want to insert whenever the code prompts (for instance, entering2 to insert two rows):
6. After confirming your choices, blank rows will be inserted into your data at the defined intervals. See the before and after comparison below:
 |  |  |
Tips & Notes:
- This macro allows you to flexibly select your data range and specify both the row interval and the number of blank rows to insert.
- Be sure to back up your worksheet or try this on a sample sheet first, as inserting rows is irreversible unless you use the Undo command immediately.
Troubleshooting: If you encounter an error, ensure your data is not filtered and that no tables overlap the selected area. If unexpected results occur, use Undo immediately to revert changes.
Insert specific number of blank rows into data range based on cell values with VBA code
Sometimes your workflow might require inserting blank rows according to a predefined list of cell values, such as inserting blanks after each record based on matching criteria. This VBA solution is ideal when the insertion logic is not based on fixed intervals but on the values within an adjacent column.
✅ When to use:
- Recommended when your data is organized by categories (e.g., departments, project phases, or sales reps) and you need to insert spacing or blank rows between groups.
- Ensure your selection is accurate, as the macro will use the selected cells to determine where to insert rows.
1. Hold down the ALT + F11 keys in Excel, opening the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Insert specific number of blank rows based on a list of numbers:
Sub Insertblankrowsbynumbers ()
'UpdatebyExtendoffice
Dim xRg As Range
Dim xAddress As String
Dim I, xNum, xLastRow, xFstRow, xCol, xCount As Long
On Error Resume Next
xAddress = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Select the spefic number column to use(single column):", "Kutools For Excel", xAddress, , , , , 8)
If xRg Is Nothing Then Exit Sub
Application.ScreenUpdating = False
xLastRow = xRg(1).End(xlDown).Row
xFstRow = xRg.Row
xCol = xRg.Column
xCount = xRg.Count
Set xRg = xRg(1)
For I = xLastRow To xFstRow Step -1
xNum = Cells(I, xCol)
If IsNumeric(xNum) And xNum > 0 Then
Rows(I + 1).Resize(xNum).Insert
xCount = xCount + xNum
End If
Next
xRg.Resize(xCount, 1).Select
Application.ScreenUpdating = True
End Sub
3. After pasting, press F5 to run the code. In the dialog box that appears, select the list of numbers (or relevant cells) upon which the blank rows should be inserted.
4. Then, click OK and you will see the intended result, as shown in the following screenshots:
 | |  |
Error reminder: If the reference list contains empty cells or non-numeric data, check the code logic or clean your data before running this macro.
Insert specific number of blank rows into data range at fixed intervals with a handy feature
If you prefer not to use VBA or want a more visual and interactive method, Kutools for Excel provides a dedicated Insert Blank Rows & Columns feature. With simple clicks, you can add blank rows or columns at any set interval throughout your data range—significantly boosting productivity, especially in repetitive reporting or formatting tasks.
After installing Kutools for Excel, follow these steps:
1. Select the data range where you need blank rows to be inserted at regular intervals.
2. Go to Kutools > Insert > Insert Blank Rows & Columns, as shown below:
3. In the Insert Blank Row & Columns dialog box, choose Blank rows under Insert type, then specify both the interval and the number of blank rows to insert, as illustrated:
Note: The setting is flexible. If you need two blank rows after every four data rows, set the interval as4 and number of blank rows as2.
4. Then click the OK button. The blank rows will be inserted within the selected range at the interval you specified, as demonstrated below:
| | |
Advantages: This solution is user-friendly, requires no coding, and is particularly helpful for those who need to frequently adjust row or column structures.
Download and free trial Kutools for Excel Now !
Insert specific number of blank rows at fixed intervals with Excel formulas
If you are unable to use macros or add-ins, you can still achieve the goal of inserting blank rows at fixed intervals using helper columns and built-in Excel functions like MOD and ROW. This method is especially useful when scripting is restricted but basic formula usage and sorting are available.
✅ When to use: Ideal for users who want to keep workbooks macro-free, need broad compatibility, or are preparing data for import/export workflows that require visual separation via blank rows.
⚠️ Precautions: This method does not actually insert blank rows but simulates them using additional rows and helper formulas. You’ll need to sort the data afterward to arrange the simulated blanks. It’s a workaround suitable for small-to-medium datasets.
Steps:
- Suppose your data is in cells A2:B11. In a new helper column (e.g., column C starting from C2), enter the following formula:
Replace=INT((ROW()-ROW($A$2))/(n+1))nwith the interval at which you want to insert a blank row. For example, to insert one blank row after every 3 rows, use:=INT((ROW()-ROW($A$2))/3) - Drag the formula down alongside your dataset to assign group numbers.
- Below the last data row, add extra rows with blank values in columns A and B. In column C (the helper column), input group numbers that match those already present, so that these blank rows are grouped correctly.
- Select the full range (original + added rows), then sort by the helper column and apply any secondary sort if needed. The blank rows will now appear after every n data rows.
🟡 Error reminder:
- Be sure to assign the correct group numbers to your blank rows—mismatches will cause them to appear in unintended places.
- Remember, this method adds physical rows only after sorting, so confirm the final layout before saving or exporting.
Summary and tips: While not as efficient as using VBA or tools like Kutools, this formula-based method is universally compatible and does not rely on scripts. For infrequent or lightweight tasks, it offers a simple and flexible workaround.
Copy and insert rows multiple times based on specific numbers with VBA code
If your task requires not just inserting blank rows but also duplicating and inserting existing data rows a certain number of times (perhaps based on a separate list of numbers), the following VBA code provides a practical automated method. This is particularly useful for replicating entries for sales orders, scenarios, or resource planning.
 | |  |
To implement this, follow these detailed steps:
1. Hold down ALT + F11 in Excel to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and then paste the code below into the module window.
VBA code: Copy and insert rows multiple times based on specific numbers:
Sub CopyRows()
'UpdatebyExtendoffice
Dim xRg As Range
Dim xCRg As Range
Dim xFNum As Integer
Dim xRN As Integer
On Error Resume Next
SelectRange:
xTxt = ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Select the list of numbers to copy the rows based on: ", "Kutools for Excel", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
If xRg.Columns.Count > 1 Then
MsgBox "Please select single column!"
GoTo SelectRange
End If
Application.ScreenUpdating = False
For xFNum = xRg.Count To 1 Step -1
Set xCRg = xRg.Item(xFNum)
xRN = CInt(xCRg.Value)
With Rows(xCRg.Row)
.Copy
.Resize(xRN).Insert
End With
Next
Application.ScreenUpdating = True
End Sub
Precautions: Always back up your data before running VBA scripts that duplicate rows, as they can quickly multiply data and may not be easily undoable.
3. After the code is in place, press F5 to run the macro. In the dialog box, select the list of numbers that represents how many times you want each row to be duplicated and inserted.
4. Click OK. The specified number of rows will be copied and inserted under each original row, as illustrated below:
| | |
Tip: This solution is best for data preparation tasks and not ideal for real-time entry, as VBA macros cannot be undone if further unrelated operations are performed.
Copy and insert rows multiple times based on specific numbers with an amazing feature
For those who want the process of duplicating and inserting rows to be intuitive and require no programming skill, the Kutools for Excel feature, Duplicate Rows / Columns based on cell value, is an efficient alternative. This tool enables you to specify which rows or columns to duplicate and how many times to do this, all based on a reference cell value or list with just a few simple selections.
After installing Kutools for Excel, follow these steps:
1. Go to Kutools > Insert > Duplicate Rows / Columns based on cell value as shown:
2. In the Copy and insert rows & columns dialog box, select Copy and insert rows as your type, indicate the data range you want to duplicate, and then specify the value list to use as the duplication lookup, as displayed:
Tip: Use this for generating test samples, replicating transaction templates, or quickly multiplying settings or records without complex scripting.
4. Finally, click OK or Apply to generate the output as demonstrated below:
| | |
Note: Ensure your value list aligns correctly with your data to achieve expected results.
Download and free trial Kutools for Excel Now !
More relative articles:
- Copy And Insert Row Multiple Times Or Duplicate The Row X Times
- In your daily work, have you ever tried to copy a row or each row and then insert multiple times below the current data row in a worksheet? For example, I have a range of cells, now, I want to copy each row and paste them3 times to the next row as following screenshot shown. How could you deal with this job in Excel?
- Insert Blank Rows When Value Changes In Excel
- Supposing you have a range of data, and now you want to insert blank rows between the data when value changes, so that you can separate the sequential same values in one column as following screenshots shown. In this article, I will talk about some tricks for you to solve this problem.
- Insert A Blank Row After Specific Text In Excel
- If you want to insert blank rows after a specific text as following screenshot shown, how to deal with it quickly and easily without inserting them manually one by one?
- Copy Rows From Multiple Worksheets Based On Criteria Into A New Sheet
- Supposing, you have a workbook with three worksheets which have the same formatting as below screenshot shown. Now, you want to copy all the rows from these worksheets which column C contain the text “Completed” into a new worksheet. How could you solve this problem quickly and easily without copying and pasting them one by one manually?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in