Add Buttons To Delete Rows Excel VBA Macro

We have already seen how to add buttons to a specific range or cell in Excel using VBA macros (Add Buttons To Fit Selection). In this post, we are going to add buttons to each row with data in a worksheet, and assign another macro to each of them to delete the respective row when clicked. Thus, there is a first macro to add the buttons, and a second macro to delete rows.

Macro code:

 

  Sub AddBtnToRows()

      Dim entryRow As Integer, cell As Range, btn As Object

      For entryRow = 2 To Range("A1").CurrentRegion.Rows.Count

          Set cell = Range("P" & entryRow)

          cell.Value = "btn" & entryRow

          Set btn = ActiveSheet.Buttons.Add(cell.Left, cell.Top, cell.Width, cell.Height)

          With btn

              .Caption = "Delete"

              .Name = "btn" & entryRow

              .OnAction = "DeleteBtnRow"

          End With

      Next entryRow

  End Sub

 

  Sub DeleteBtnRow()

      Dim btnName As String, target As Range

      btnName = Application.Caller

      Set target = Columns("P").Find(btnName)
      Rows(target.Row).Delete

  End Sub

 

Macro explained:

• We declare an Integer variable to store the row number for each entry (entryRow), an Range variable to assign the position where each button is added (cell), and an object variable to assign each added button (btn).
• We start a loop from row 2 (to skip headers), to the last row in the data set, obtained from the count of rows in the region with data around A1. CurrentRegion gets the region with data delimited by any blank rows and columns.
• For every entry, we set the variable cell to the corresponding cell in column P for each row, which is then used to add the button within that cell. Column P is the one after the last column with data, we use that column to add the buttons. 
• Then we add a reference for the name of the button in that cell with “btn” & entryRow. That would be btn2 for row 2, btn3 for row 3, and so on. 
  
• We add the button to fit the cell in column P and row entryRow, referring to the four properties of the cell (Left, Top, Width, Height). We have used the same principle in several other posts (for example Add Button To Fit Selection or Add Shapes To Selection)
• We set each added button to the variable btn, and then apply the following properties/methods:
• First, we change the caption of the button to “Delete”
• Second, we give each button a different name, which is the same as the value we added before to that cell. That name is important to identify which button was clicked (in the second macro).
• Third, we assign an action or a macro to be run when the button is clicked. That is the DeleteBtnRow macro.
• DeleteBtnRow uses the Caller property of the Application object to return the name of the object that triggered the macro. That’s the name of the button that was clicked.
• Then we search that name in column P, and find the target cell which button was clicked.
  
• Finally, we get the row of the target cell as the index number of the row to be deleted.
• All added buttons in a worksheet can be deleted if needed using the following expression: ActiveSheet.Buttons.Delete

 

This is how we add buttons to delete rows in Excel using VBA macros.

 

Other examples:

• How to Compare Two Workbooks with Excel VBA macros
  
• How to Copy/Paste Charts To PowerPoint with Excel VBA macros
  
• How to Update Web Query On File Open with Excel VBA macros