Copying formulas when inserting rows

Question

Is there a way to copy the formulas into the format when inserting a row between 2 rows, like such:

         Range("E11:N" & addRowCount).Select

        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

I want to not only copy the original "xlFormat" from "Above", I would like the "formulas" to be copied in the process. Is this possible?

Thanks

Answer 1

Hi Ayoade,

Thanks for posting in the community, I’m glad to offer help.

If we convert a range to a table, formulas will automatically be filled into the next newly inserted row.

 

In your case, if you prefer to use VBA, you can get some ideas from the following articles according to your situation:

-      Copying formula to the next row when inserting a new row

-      How To Autofill Formula When Inserting Rows In Excel?

However, if the above information doesn’t help and you need further assistance on this, considering VBA is beyond our support scope, I can help you move this thread to Excel/Microsoft Office Programming/Office 2019 category where has more professional members, we also welcome anyone who has the related experience to share ideas here, we appreciate your contribution.

Please feel free to post back if you have any concerns.

Best Regards,

Arck

Answer 2

This looks very interesting. I will try it and let you know how it went.

Great and thanks.

Answer 3

Yes I want to insert 3 rows above row 11, but I want those 3 new inserted rows to also contain the formats and formulas that are in row 10.

Sub Test()

  Dim Base As Range, CopyFrom As Range

  Dim AddRowCount As Integer

  'Customize this to your needs:

  'Number of rows to insert

  AddRowCount = 3

  'Refer to the row / cells which should be shift down

  Set Base = Range("E11:N11")

  'No need to customize this:

  'Just to be sure

  Set Base = Base.Rows(1)

  'Refer to the row above

  Set CopyFrom = Base.Offset(-1)

  'Insert the rows

  Base.Resize(AddRowCount).Insert

  'Fill anything down

  CopyFrom.Resize(AddRowCount + 1).FillDown

End Sub

Answer 4

Yes I want to insert 3 rows above row 11, but I want those 3 new inserted rows to also contain the formats and formulas that are in row 10. Since the new rows will be inserted after row 10

Answer 5

Your code says you use addRowCount as the end row number but as a descriptor, addRowCount sounds like the number of rows you want to insert.

For example, if addRowCount is 13, do you want to insert 3 rows (e.g. Range("E11:N" & addRowCount).Insert) or 13 rows (e.g. Range("E11:N11").Resize(addRowCount, 1).Insert)?