Results 1 to 4 of 4

Thread: button to delete row and update another row

  1. #1
    New Member
    Join Date
    Sep 2015
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default button to delete row and update another row

    Hello,

    I want if it contains a predefined value in a cell, to delete that row but not before to add the value that it has to the upper one, example below:


    A B C
    1 apples 30
    2 orange 105
    3 test 5
    4 carrot 40
    5 test 10


    Will result like this:

    On B3 it has "test" and value on C with 5, sum it with the upper one and delete complete the row afterwards (with test)
    Same for B5.


    I want like this:



    A B C
    1 apples 30
    2 orange 110
    3 carrot 50


    I would like to have a button for this one.


    Thank you

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,060
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: button to delete row and update another row

    See if this does what you want:
    Code:
    Sub MyDeleteMacro()
    
        Dim lr As Long, r As Long
        Dim str As String
        
        Application.ScreenUpdating = False
        
    '   Designate column B value to delete
        str = "test"
        
    '   Find last cell in column B with data
        lr = Cells(Rows.Count, "B").End(xlUp).Row
        
    '   Loop through all rows from bottom to top
        For r = lr To 2 Step -1
    '       Check to see if row is one we are looking for
            If Cells(r, "B") = str Then
    '           Add value from column C to row above
                Cells(r - 1, "C") = Cells(r - 1, "C") + Cells(r, "C")
    '           Delete row
                Rows(r).Delete
            End If
        Next r
        
        Application.ScreenUpdating = True
        
    End Sub
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Sep 2015
    Posts
    44
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: button to delete row and update another row

    Works like a charm Thank you :D

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,060
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: button to delete row and update another row

    You are welcome.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •