button to delete row and update another row


New Member

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:

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:

1 apples 30
2 orange 110
3 carrot 50

I would like to have a button for this one.

Thank you


MrExcel MVP, Junior Admin
See if this does what you want:
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
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...