Delete Data from Data Validation list and delete all other refernces

[philkeoghan]

Hi
I have a situation which I have made work sort of, but would like it to be dynamic and improved if possible though this forums great help
Thanks in advance

What I have is a list of names, these sometimes get deleted as people leave or added to when new staff come on board

What I need to be able to do is if I delete a name out of a named range(Master List) that is used in my Data Validation lists. I need to be able to find all refences to what I deleted and delete them too.
These lists are on multiple sheets.

This is what I have some up with from a bit of macro recording and a couple of changes in VBA

Sub DeleteValTest()
'
' DeleteValTest Macro
'

'
    Deleterow = ActiveCell.Row
    DeleteData = ActiveCell.Value
    Rows(Deleterow).Select
    Selection.Delete Shift:=xlUp
    Sheets("Sheet2").Select
    Range("D1").Select
    Selection.SpecialCells(xlCellTypeSameValidation).Select
    Cells.Replace What:=DeleteData, Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

Sheet 1 has a list of names on the event of a cell having the delete key pressed I want this code to run.

It seems to work ok if my lists are just on sheet 2 in column d but they will be on other sheets as well in different areas

Any ideas on what I could do??
Thanks
Phil

 

[jbeaucaire]

This is how I would do this. Open your master list sheet, select a cell, then run this macro, it was ask you for confirmation, then it will clear every cell with that value in it.

Option Explicit

Sub DeleteValidatedSelectionFromAllSheets()
Dim MyVal As String
Dim valFIND As Range
Dim ws As Worksheet

MyVal = ActiveCell.Value
If MsgBox("Search all sheets for instances of '" & MyVal & "' and remove them?" _
    & vbLf & "This value will be removed from this sheet as well.  Proceed?", _
    vbYesNo + vbQuestion) = vbNo Then Exit Sub
    
On Error Resume Next
For Each ws In Worksheets
        Do
            Set valFIND = ws.Cells.Find(MyVal, LookIn:=xlValues, LookAt:=xlWhole)
            If Not valFIND Is Nothing Then
                valFIND = ""
                Set valFIND = Nothing
            Else
                Exit Do
            End If
        Loop
Next ws

End Sub

 

[philkeoghan]

That is fantastic
Thank you