Delete Range of Rows on Cell Click

Question

I would like a macro, that runs when a cell with the text value "DELETE" is clicked, and deletes entire the range of rows from one cell above and 19 cells below the cell with the value "DELETE".

For example if cell X7 has the value "DELETE", when it is clicked rows 6:26 are deleted.

The code I have so far is:

Private Sub Delete_Type(ByVal Target As Excel.Range)

    If Target.Address = "$X$7" Then
        Rows("(Row(), Column(),1, 4):((Row(), Column(), 4)+19)").Select
        Selection.delete Shift:=xlUp
    End If

End Sub

Dmitry Pavliv Dmitry Pavliv · Accepted Answer · 2014-04-03T08:32:21

Try this one:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim firstRow As Long, lastRow As Long

    Application.EnableEvents = False
    On Error GoTo ErrorHandler


    If UCase(Target.Value) = "DELETE" Then
        'if target cell is in first row of sheet, we can't get row above
        firstRow = Application.Max(Target.Row - 1, 1)
        'if target cell is in last 19 rows of sheet, we can't get 19 rows below
        lastRow = Application.Min(Target.Row + 19, Rows.Count)

        Range(firstRow & ":" & lastRow).Delete Shift:=xlUp
    End If

ExitHere:
    Cancel = True
    Application.EnableEvents = True
    Exit Sub
ErrorHandler:
    Resume ExitHere
End Sub

As name of event Worksheet_BeforeDoubleClick suggests, it fires every time before double click on any cell.

Put above code in Sheet module:

enter image description here

Delete Range of Rows on Cell Click

1 Answers