Automatic population of the formula from the cell above when inserting new cell

[IrishMen]

Hi

I am sorry to bother people with a question that has been asked a hundred times.
I have data in column D and Trim(D) in column E (column E is supposed to be hidden for users)
Therefore I need to make sure that if someone inserts a row in between - it will be filled up with formula from above, however cell reference will need to add 1..
I did look through other posts and tried to amend their codes, however, I was unsuccessful.
I imagine that logic would be something like: copy formula from the cell above (which is going to be Trim(D(n+1)) while there is a number in the respective D cell, otherwise exit that loop.
Could you please help me with the code?

Thank you for your time

 

[vds1]

I think if you format the data into table then formulas will be automatically populated whenever there is new data inserted.

 

[IrishMen]

I just did, but unfortunately when i tested - some of the duplicates have not been removed.. Any idea what could affect?

Table

I think if you format the data into table then formulas will be automatically populated whenever there is new data inserted.

 

[vds1]

I just did, but unfortunately when i tested - some of the duplicates have not been removed.. Any idea what could affect?

Table

Let’s say you have data in column D and trim formula in column E.
Select column D & E > Go to Insert Table > select my data has headers > click ok
Now add value at the end of column D then you will have formula populated in column E.

 

[IrishMen]

Thanks for your effort to help me out.
If you insert data at the very end of all data contained in column E - it works even if this is not a table.
However, you are right and it does populate formula if I insert a row in the middle of my data. But, the issue is when I tested removing duplicates - it missed one, that is the reason why I am not keen to use the table, since I am not sure what affected that number that hasn't been removed..

Let’s say you have data in column D and trim formula in column E.
Select column D & E > Go to Insert Table > select my data has headers > click ok
Now add value at the end of column D then you will have formula populated in column E.

 

[ParamRay]

.
.

For a VBA solution, paste the following code into the code module corresponding to that worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rang As Range
    Dim cell As Range
    
    If Target.Address Like "$#*:$#*" Then
        With Me
            On Error Resume Next
            Set rang = Intersect(.Columns("D"), .UsedRange)
            On Error GoTo 0
        End With
        If Not rang Is Nothing Then
            For Each cell In rang
                cell.Offset(0, 1).Formula = _
                    "=TRIM(" & cell.Address(False, False) & ")"
            Next cell
        End If
    End If
    
End Sub

 

[IrishMen]

OMG! You are amazing
Thank you, it works, the only thing: i cannot undo or redo when inserting/removing rows...
Is there an easy solution for that?

Thank you

 

[ParamRay]

.
.

Sorry, no. You won't be able to undo if you insert or delete a row...

 

[IrishMen]

Oh, that is ok.
Thank you very much.

.
.

Sorry, no. You won't be able to undo if you insert or delete a row...