VBA Confirm Message Before Deleting Row

[Admin]

Hi

Welcome to ExcelFox!!

Try

Option Explicit

Sub Delete_Row()
    Dim calcmode As Long
    Dim ViewMode As Long
    Dim myStrings As Variant
    Dim FoundCell As Range
    Dim I As Long
    Dim ws As Worksheet
    Dim strToDelete As String
    Dim DeletedRows As Long
    Dim c       As Range
    Dim fa      As String
    
    'for speed purpose
    With Application
        calcmode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
        
    'back to normal view, do this for speed
    ViewMode = ActiveWindow.View
    ActiveWindow.View = xlNormalView
    'Turn off Page Breaks, do this for speed
    ActiveSheet.DisplayPageBreaks = False
    
    'search strings here
    strToDelete = Application.InputBox("Enter value to delete", "Delete Rows", Type:=2)
    If strToDelete = "False" Or Len(strToDelete) = 0 Then
        ActiveWindow.View = ViewMode
        With Application
            .ScreenUpdating = True
            .Calculation = calcmode
        End With
        Exit Sub
    End If
    
    'make search strings array for more than one
    myStrings = Split(strToDelete)
    
    'Loop through selected sheets
    For Each ws In ActiveWorkbook.Windows(1).SelectedSheets
        
        'search the values in MyRng
        For I = LBound(myStrings) To UBound(myStrings)
            Set c = ws.UsedRange.Find(What:=myStrings(I), _
                                                  LookIn:=xlFormulas, _
                                                  LookAt:=xlWhole, _
                                                  SearchOrder:=xlByRows, _
                                                  SearchDirection:=xlNext, _
                                                  MatchCase:=False)
            Set FoundCell = Nothing
            If Not c Is Nothing Then
                fa = c.Address
                Do 'Make the loop
                    If FoundCell Is Nothing Then
                        Set FoundCell = c
                    Else
                        Set FoundCell = Union(FoundCell, c)
                    End If
                    DeletedRows = DeletedRows + 1   'Count deleted rows
                    'search the used cell/range in entire sheet
                    Set c = ws.UsedRange.FindNext(c)
                Loop While Not c Is Nothing And c.Address <> fa
            End If
        Next I
        If Not FoundCell Is Nothing Then
            If MsgBox("Would you like to delete (" & FoundCell.Areas.Count & ") Rows?", vbQuestion + vbYesNo) = vbYes Then
                FoundCell.EntireRow.Delete
            End If
        End If
    Next ws
    If DeletedRows Then
        MsgBox "Number of deleted rows: " & DeletedRows, vbInformation, "Delete Rows Complete"
    Else
        MsgBox "No Match Found!", vbInformation, "Delete Rows Complete"
    End If
    
    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = calcmode
    End With
    
End Sub

[mackypogi]

Hi Sir,

Thank you for the codes admin!.

It works! but when I click the 'No' in the confirmation MsgBox it still show the number of deleted rows even if it does not delete any rows. It is almost done

[mackypogi]

The problem is when I press 'NO'

msgbox1.jpg

It shows the MsgBox

msgbox2.jpg

Thank you so much for help admin!