Insert a new row with same formulae

abssorb

New Member
Joined
Apr 15, 2008
Messages
22
I have a worksheet with about 30 cols of data user by others. 20% of the cols are actual input data, the rest are formulae, lookups etc.

When a new row is inserted, it's important that the formulae are copied down, so I've added a macro to try and achieve this. I'm not great with VBA, so a crude macro inserts a new row below the current one, copies current to next one down, and then deletes the copied content.

However this is unsafe. If an autofilter is applied for instance the wrong row gets selected and gets deleted. How can I be sure to select the next actual row, and not the one that autofilter thinks is next?
Or maybe there's a better way altogether? Guidance welcome.

VBA Code:
Dim myCell
Set myCell = ActiveCell

    ' Use current selected cell, or selected row, insert a new row BELOW it and copy down
        Rows(ActiveCell.Row).Select
        Selection.Copy
        Selection.Insert Shift:=xlDown
   
    ' For the row just created, select any text or number (exclude formulae) and clear the contents.
        On Error GoTo HandleBlanks
        Rows(ActiveCell.Row + 1).SpecialCells(xlCellTypeConstants).Select
          Exit Sub
        Selection.ClearContents
       
HandleBlanks:
    ' This is here for error trapping. In case user runs the same macro twice, which means it will run on an empty row with
    ' nothing to copy, which will cause 1004 Error: No cells were found
             Exit Sub

End Sub
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,034
Office Version
  1. 365
Platform
  1. Windows
Have you considered setting this up as an actual Table in Excel?
If you do that, then whenever you insert a new row within your table, Excel will automatically copy the formulas while leaving hard-coded fields blank.

Will that do what you want?
 

abssorb

New Member
Joined
Apr 15, 2008
Messages
22
Unfortunately no, the sheets are highly formatted and annotated and there's little I can do about that.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,034
Office Version
  1. 365
Platform
  1. Windows
Then your best bet is probably to update your VBA code to remove the filters before inserting/deleting anything.
 

abssorb

New Member
Joined
Apr 15, 2008
Messages
22
I've been researching that, so it's good to know.
I was hoping it was possible to explicitly state: insert a row absolutely at row 21, instead of relative to the current position. So easy to do in a formula, I'm quite surprised I can't find an equivalent in VBA.

But - removing filters is OK.
My next challenge is, autofilters are on, but not applied = desired state.
I can't find a way to interact with this. I can only find how to completely switch off autofilters.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,034
Office Version
  1. 365
Platform
  1. Windows
I was hoping it was possible to explicitly state: insert a row absolutely at row 21, instead of relative to the current position. So easy to do in a formula, I'm quite surprised I can't find an equivalent in VBA.
If you want to target row 21, you can replace this:
VBA Code:
        Rows(ActiveCell.Row).Select
with this:
VBA Code:
        Rows(21).Select
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

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
Top