How to insert blank rows based on above cell values in Excel?

In Excel, you might need to insert blank rows below cells based on their numeric values. Doing this manually can be time-consuming, but with a simple VBA macro, you can automate the process and quickly insert the required number of blank rows below each cell in a selected range, as shown below.

Insert rows based on cell values

Insert rows based on cell values

If you want to insert blank rows based on cell values, you can use a macro code.

1. Enable the sheet you use and press the Alt + F11 keys to open Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the below code into the script.

Sub Insert()
'UpdatebyExtendoffice20170926
    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 a range 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. Press F5 key to run the code, and a dialog pops out to remind you to select a list to work. See screenshot:

4. Click OK, the blank rows have been inserted.