Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in


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.

doc insert blank row by above number 1 shot arrow right doc insert blank row by above number 2

Insert row based on value above

Insert row based on value above

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()
    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
    xRg.Resize(xCount, 1).Select
    Application.ScreenUpdating = True
End Sub

doc insert blank row by above number 3

3. Press F5 key to run the code, and a dialog pops out to remind you to select a list to work. See screenshot:
doc insert blank row by above number 4

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...)

doc insert blank row

Recommended Productivity Tools

Office Tab

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

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Natasha · 4 months ago
    Hi there, thank you so much for the post above, is there a way to run the code automatically without having to to press F5?
  • To post as a guest, your comment is unpublished.
    Rageesh · 6 months ago
    Thank you so munch, 1 st format working fine....