How to insert blank row based on above cell value in Excel?

In Excel, the inserting blank rows are one of the most usually used functions in our daily work. However, is there any way to quickly insert blank rows based on above cell value as below screenshot shown? Now, this article, talks about the method to quickly insert blank row based on the above cell values in Excel.

Insert row based on value above

Insert row based on value above

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!

If you want to insert blank row based on the number above, you can use a macro code.

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

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

VBA: Insert blank code based on cell above

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.

Insert Blank Rows & Columns(insert multiple blank rows/columns at once, or insert blank rows/columns every nth row/col...)

Go to Download Free Trial 60 daysPurchase PayPal / MyCommerce

Recommended Productivity Tools

Office Tab

Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel