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...)
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
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...
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.· 4 months 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.· 6 months agoThank you so munch, 1 st format working fine....