UserForm VBA that Deletes Multiple Rows from worksheet

[powerpivotlegal]

Hello,

I've been slowly building out multiple user forms through the help of the community. I need a Delete Command Button in my Search User Form that loops through my worksheet and deletes multiple rows that equal the Project Name selected in the Search User Form.

For example, user selects "Project XYZ" from the combo box Search User Form. Project XYZ is listed twice in the worksheet under Column A because there are multiple team members in Column C (row 100 is for Team Member Joe and row 101 is Team Member Jane).
When the user then clicks on the Delete Command Button to delete Project XYZ, the code that I found below only deletes row 100 and not both rows 100 and 101.

How can the code be fixed to search through a dynamic range where Column A is expanding and delete all rows that equal the value in Column A?


Private Sub DeleteCommandButton_Click()
Dim cName As String
Dim i As Long


cName = ProjectNameSearchComboBox.Value


For i = 1 To 200


If Range("A" & i).Value = cName Then
ans = MsgBox("Delete " & cName & " from list?", vbYesNo)
If ans = vbYes Then
Range("A" & i).EntireRow.Delete
End If
End If
Next i
End Sub

Many thanks in advance!

- James

 

[pbornemeier]

Filter column A by the item you want to delete and if any rows match, delete them.

Sub DeleteMatchesFromColumnA(sProjectToDelete As String)
    
    Dim lDelCount As Long

    With ActiveSheet
        .AutoFilterMode = False
        .Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=sProjectToDelete, Operator:=xlAnd
        lDelCount = Application.WorksheetFunction.Subtotal(3, .Columns(1)) - 1  '-1 to not count header
        If lDelCount > 0 Then
            'row other than header is visible
            .Range("A1").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            'Offset in above line to preserve row 1, headings
        End If
        .AutoFilterMode = False
        Debug.Print lDelCount & " instances of " & sProjectToDelete & " in column A were deleted."
    End With
    
End Sub