Macro to delete entire row and tell me what row i'm deleteing

[tonywatsonhelp]

Hi Everyone,
I want a macro that can do this,
I select a cell,
run the macro and it deletes entire row of cell i selected, but before, i get a message box saying "This will delete row 56" (or whichever row ive selected) and say "Which is client" and the data from that row column H, do you wish to proceed? yes/no

Please help iif you can

Thanks

Tony

 

[tyija1995]

Hi tonywatsonhelp,

This code should do what you have asked for:

Sub DeleteActiveCellsRow()
    Dim activeRowNumber As Long
    Dim iAns As VbMsgBoxResult
    
    activeRowNumber = ActiveCell.Row
    Rows(activeRowNumber).Select
    
    iAns = MsgBox("This will delete row " & activeRowNumber & " Which is client " & _
    Cells(activeRowNumber, 8) & " do you wish to proceed?", vbYesNo + vbQuestion, "Delete Row?")
    
    If iAns = vbYes Then
        Selection.Delete Shift:=xlUp
    Else
        'Do nothing
    End If
End Sub

 

[tonywatsonhelp]

Thank you Tyija1995, this is perfect

Tony

 

[jim may]

Here is another way -- to work with Events - using the Before Right-Click Worksheet Event
Paste the below code into your sheets code window.
If you have a header row(s) - watch in code line 4 where I use Row # (This is Excel Row Number) change accordingly.....

Try it in a BACK-UP copy of your file before moving forward...

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Application.Intersect(Target, Columns("H")) Is Nothing Then Exit Sub
Cancel = True
res = MsgBox("You are about to Delete Row #: " & Target.Row & vbNewLine & _
    "with the Client name: " & Target.Value & "." & vbNewLine _
    & "Do you wish to proceed?", vbYesNo + vbQuestion)
    If res = 6 Then
        Target.EntireRow.Delete
    End If
End Sub