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

IrishMen

New Member
Joined
Sep 17, 2014
Messages
10
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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,193
I think if you format the data into table then formulas will be automatically populated whenever there is new data inserted.
 

IrishMen

New Member
Joined
Sep 17, 2014
Messages
10
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

Well-known Member
Joined
Oct 5, 2011
Messages
1,193
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

New Member
Joined
Sep 17, 2014
Messages
10

ADVERTISEMENT

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

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
.
.

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

Code:
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

New Member
Joined
Sep 17, 2014
Messages
10

ADVERTISEMENT

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

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
.
.

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

Watch MrExcel Video

Forum statistics

Threads
1,108,924
Messages
5,525,657
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top