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:

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:

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.

1 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

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy