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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,200
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,200
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...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,981
Members
430,100
Latest member
namhnz

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