Excel – How to add new row in Excel *below* the current one

microsoft excelmicrosoft-excel-2013

The Insert > Insert Sheet Rows command in Excel always inserts a row above the current row. However, it is quite a common scenario for me to want to insert a row below the current row, like this:

enter image description here

The important detail is that the border was pushed down and that the formula was updated to include 3 cells instead of the original two. When I do it naively, by placing the cursor at the totals line and add a row there, I get this:

enter image description here

The border and formula are "broken" here and I need to fix them manually. What are my options?

Note: A "table" (Ctrl+T) would help with this but in my case, I need a generic solution that does not depend on the rows being part of a table.

Best Answer

If you place your cursor on the row you want to insert below, this will do that and copy the format of that row:

Sub InsertRowBelow()

    Application.ScreenUpdating = False

    ActiveCell.Offset(1, 0).EntireRow.Insert
    ActiveCell.EntireRow.Copy
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

    Application.ScreenUpdating = True

End Sub
Related Question