Delete row - but to include any cells that have data validation / lists applied.

Hi - I currently have the below macro which works really well in deleting entire rows. It first highlights the selected rows for deletion, and then prompts a message box for users to confirm or cancel deletion. Neat

What I have noticed though is that once the rows are deleted, any cells that had lists applied to them (although they are being 'cleared') are not having the data validation / drop down lists removed.
Is there any additional code I can add to clear these too? Thanks in advance, Matt.


Private Sub Remove_Recipient_Click()

Dim c As Range, DltRng As Range
For Each c In Selection
If DltRng Is Nothing Then
Set DltRng = c
Else
Set DltRng = Union(c, DltRng)
End If
Next c
If Not DltRng Is Nothing Then DltRng.EntireRow.Interior.ColorIndex = 6
Rply = MsgBox("Warning - This can not be undone!" & vbNewLine & vbNewLine & "Are the highlighted row(s) the ones you want to delete?" & vbNewLine & vbNewLine & "If not click NO and first select any cell from the appropriate row(s)", vbYesNo)
If Rply = vbYes Then
DltRng.EntireRow.Delete
ActiveCell.Select
Else
DltRng.EntireRow.Interior.ColorIndex = xlNone
MsgBox "No rows were deleted."
End If
End Sub

AlphaFrog - ah I see!!

Your code is far more simplified than mine so I have implemented straight away and it works just the same so thank you

You are right about the DV cells too, on further interrogation they seem to be added when I run my 'Add new row' macro (see below). Because this is inserting a row to the bottom of a table I think its copying the DV down too. Wonder if there is a way to stop this to save having to delete more rows every time?

I will attach the file to assist with the above explanation!

Private Sub New_Row_Click()

Range("A6:L6").Copy
Range("B" & Rows.Count).End(xlUp).Select
ActiveCell.EntireRow.Offset(1, 0).Insert
Application.CutCopyMode = False

Range("B5").End(xlDown).Select

MsgBox "New Document added successfully "

End Sub

Thanks again for looking at this.

I definitely do want the new row to have them, but not the row under this.

For some reason when adding a new row, both the new row and the one beneath this BOTH get the DV's applied???