Command button in a userform to delete multiple rows depending on selection in listbox

Question

I am currently working on a userform which has a "Clear Process" command button. The idea is that my userform has a listbox which will list all of the current processes.

From here the user will select which process(es) he/she would like to clear from the worksheet (delete all rows relating to the process).

Embedded in the code I have used the word "Lisa" as a point of reference for the previous userform to know which cell the Process Name should be, using the offset function.

I would like to be able to use the word "Lisa" once the process to be deleted has been identified by the user. This would always be the row where "Lisa" is found and 19 rows below.

I have started some code but when trying to find "Lisa" depending on the selection made by the user I came across an issue.

Private Sub ClearButton_Click()

Dim findvalue As Range
Dim cDelete As VbMsgBoxResult

'hold in memory
Application.ScreenUpdating = False

'check for values
If Emp1.Value = "" Or Emp2.Value = "" Then
    MsgBox "There are no processes to delete"
    Exit Sub
End If

'confirm process should be deleted
cDelete = MsgBox("Are you sure you want to delete this process?", vbYesNo)

If cDelete = vbYes Then
    'find the process to be deleted
    '''''''set findvalue =

'''''''delete entire process
findvalue.EntireRow.Delete
End If

End Sub

Hopefully this is enough information, any help would be greatly appreciated :)

Answer 1

If you use Named ranges for your processes, which seems to be almost mandatory in your case, then you can do the following:

Sub DeleteNamedRange(rngName As String)
    Range(rngName).ClearContents
    ThisWorkbook.Names(rngName).Delete
End Sub

Invoke the Sub this way:

Call DeleteNamedRange("Lisa")

Answer 2

Something as small as this one would set a range to a found value and delete it:

Public Sub TestMe()

    Dim findValue As Range
    Set findValue = Selection.Find("Lisa")
    findValue.EntireRow.Delete

End Sub

As a good practice, you may check whether the findValue is not nothing before deleting. Thus, you avoid an error:

If Not findValue Is Nothing Then
    findValue.EntireRow.Delete
End If

And if you want to make the code even one step further, keep in mind that the default value of the argument After in Find() is the first cell. Thus, Find() always start looking from the second cell. To avoid this, and to start looking from the first cell, it is considered a good practice to pass the After:= argument explicitly:

Public Sub TestMe()

    Dim findValue As Range
    Dim selectedValue As Range

    Set selectedValue = ActiveSheet.Range(Selection.Address)
    With selectedValue
        Set findValue = .Find("Lisa", after:=.Cells(.Cells.Count))
    End With

    If Not findValue Is Nothing Then
        findValue.EntireRow.Delete
    End If

End Sub

To make the code even more "interesting", one may decide to check whether a range is selected (a shape can be also selected). Thus, the TypeName(Selection) can be used with something like this:

If TypeName(Selection) <> "Range" Then
    MsgBox "Range is not selected!"
    Exit Sub
End If

Range.Find MSDN