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.
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...)
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 6 months agowoow really it´s working, thank yoou so much, I saved a lot of time this day
- To post as a guest, your comment is unpublished.· 1 years agoHi 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.· 1 years agoThank you so munch, 1 st format working fine....