Copy formula when inserting row in specific columns

TimvMechelen

Board Regular
Joined
Nov 7, 2016
Messages
121
Hi all,

On a specific sheet (for example "sheet ABC") in a specific workbook (for example "Workbook ABC"), I have formulas in the columns D, E, G, J and I.

Is there a possibility, when inserting row(s), to automatically copy the formulas down in the inserted rows in the specific columns?

Thanks!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Are you inserting the rows manually or with vba?
 
Upvote 0
Manually.
Then I don't think you can get your formulas in question automatically copied down.
However, you could have a macro that both inserts a row (or multiple rows) and copies the relevant formulas down. Is that something you would be interested in?
 
Upvote 0
Is that something you would be interested in?
In case you are, you could try this in a copy of your workbook.
Select a row or rows or even a cell or cells in a contiguous area where you want the new rows inserted (e.g. you could select B10:D11 if you wanted 2 rows inserted below row 9) & then run the following macro.

Code:
Sub Insert_Rows()
  With Selection
    If .Areas.Count = 1 Then
      .EntireRow.Insert
      Intersect(.EntireRow.Offset(-.Rows.Count - 1).Resize(.Rows.Count + 1), Range("D:E,G:G,I:J")).FillDown
    End If
  End With
End Sub
 
Last edited:
Upvote 0
In case you are, you could try this in a copy of your workbook.
Select a row or rows or even a cell or cells in a contiguous area where you want the new rows inserted (e.g. you could select B10:D11 if you wanted 2 rows inserted below row 9) & then run the following macro.

Code:
Sub Insert_Rows()
  With Selection
    If .Areas.Count = 1 Then
      .EntireRow.Insert
      Intersect(.EntireRow.Offset(-.Rows.Count - 1).Resize(.Rows.Count + 1), Range("D:E,G:G,I:J")).FillDown
    End If
  End With
End Sub
Thanks! it works
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top