Insert a new row with same formulae

[abssorb]

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.

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

 

[Joe4]

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]

Unfortunately no, the sheets are highly formatted and annotated and there's little I can do about that.

 

[Joe4]

Then your best bet is probably to update your VBA code to remove the filters before inserting/deleting anything.

 

[abssorb]

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]

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:

        Rows(ActiveCell.Row).Select

with this:

        Rows(21).Select

 

[Man_of_Sleep]

Hi,

I am new here.
Sorry if my post here if inappropriate.

I am trying to add new row.
I have this code.

Option Explicit
 
Private Sub CommandButton1_Click()
    addNewRow
End Sub
 
Sub addNewRow()
    
    ' Do not insert a row before the first row.
    Dim iTopRow As Integer
    iTopRow = 1
    
    If (ActiveCell.Row > iTopRow) Then

        ' Get the active row number.
        Dim rowNum As Integer
        rowNum = ActiveCell.Row
        
        Rows(rowNum).EntireRow.Insert       ' Insert a new row.
        
    End If
End Sub

But I am trying to insert the new row, with a new linked checkbox.
The checkbox is automatic linked to the new cell in the new added row.

Sorry if my problem already asked by other person before.
If already asked, can anyone point me there.

 

[Fluff]

As your question has nothing to do with this thread, please start a thread of your own. Thanks