Copy formula when inserting row in specific columns

TimvMechelen

Board Regular
Joined
Nov 7, 2016
Messages
116
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!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,627
Office Version
365
Platform
Windows
Are you inserting the rows manually or with vba?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,627
Office Version
365
Platform
Windows
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?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,627
Office Version
365
Platform
Windows
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:

TimvMechelen

Board Regular
Joined
Nov 7, 2016
Messages
116
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
 

Forum statistics

Threads
1,085,321
Messages
5,382,950
Members
401,811
Latest member
ngoctinh87

Some videos you may like

This Week's Hot Topics

Top