Thread: adding "Yes/No" message box to delete row macro

Hello,

I'm using the following code to do conditional delete row, but would like to add a message box to confirm the "delete" with a Yes/No msgbox. I'd like to have the macro exit when the user clicks "no" but I'm missing something here.
Thank you in advance for your help

Sub DeleteRowsVendorUS()
    Const sTOFIND As String = "void"
     varanswer = MsgBox("Are you sure you want to delete these rows?", vbYesNo, "alert")
    Dim rngToCheck As Range, rngCell As Range, rngToDelete As Range
    
    Application.ScreenUpdating = False
    
    With Sheet7
        Set rngToCheck = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    
    For Each rngCell In rngToCheck
        If rngCell.Value = sTOFIND Then
            If rngToDelete Is Nothing Then
                Set rngToDelete = rngCell
            Else
                Set rngToDelete = Union(rngToDelete, rngCell)
            End If
        End If
    Next rngCell
        
    If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
    
    Application.ScreenUpdating = True
End Sub

Hello Dannypak,

You just need to test the result with an If ... Then statement.

Sub DeleteRowsVendorUS()
   Const sTOFIND As String = "void"
   Dim rngToCheck As Range, rngCell As Range, rngToDelete As Range

     varanswer = MsgBox("Are you sure you want to delete these rows?", vbYesNo, "alert")
       If varanswer = vbNo Then Exit Sub
    
     Application.ScreenUpdating = False
    
    With Sheet7
        Set rngToCheck = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    
    For Each rngCell In rngToCheck
        If rngCell.Value = sTOFIND Then
            If rngToDelete Is Nothing Then
                Set rngToDelete = rngCell
            Else
                Set rngToDelete = Union(rngToDelete, rngCell)
            End If
        End If
    Next rngCell
        
    If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
    
    Application.ScreenUpdating = True
End Sub

Hi Dannypak,

Something along these lines should work...

Dim varAnswer
varAnswer = MsgBox("Are you sure you want to delete these rows?", vbYesNo, "alert")
If varAnswer = vbYes Then
   ' Put the rest of the code you want to execute here
Else
   Exit Sub
End If

Try this

Option Explicit

Sub DeleteRowsVendorUS()
    Const sTOFIND As String = "void"
    Dim rngToCheck As Range, rngCell As Range, rngToDelete As Range
    
    Application.ScreenUpdating = False
    
    With Sheet7
        Set rngToCheck = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    
    For Each rngCell In rngToCheck
        If rngCell.Value = sTOFIND Then
            If rngToDelete Is Nothing Then
                Set rngToDelete = rngCell
            Else
                Set rngToDelete = Union(rngToDelete, rngCell)
            End If
        End If
    Next rngCell
   Select Case MsgBox("Do you want to permanently remove this data?", vbYesNo Or vbQuestion Or vbDefaultButton1, "Continue")
   
    Case vbYes
       If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
    Case vbNo
   End Select
    Application.ScreenUpdating = True
End Sub