Need help writing Simple Search & Delete Macro

[evxret]

For some reason, for the life of me, I cannot find any relevant information online to what Im trying to accomplish here.
Essentially, all im trying to do is create a macro that takes the value in E2 (which will be a dropdown list), searches it in the table pictured left, finds that name & deletes it ALONG WITH the cell next to it and shifts rows up.

To create a background to this situation, employees editing this table continuously remove names and simply leave the cell blank, therefore the list is spaced out and it drives me nuts. Im simply going to lock them from editing cells on the table and create a delete name button so they have no choice but to delete & shift cells up.

Help is VERY MUCH appreciated. Thank you to this community!!

 

[Candyman8019]

Give something like this a try:

Private Sub worksheet_change(ByVal target As Range)
If Not Intersect(target, Range("E2")) Is Nothing Then
   Range("A1:A100").Find(what:=Range("E2").Value).EntireRow.Delete
End If
End Sub

One thing to note is that if they choose a name that is in A2, then your dropdown field will also be deleted...so you may want to insert a row to avoid that situation.

 

[Candyman8019]

If you only want to delete column A and B values and shift the remainder up, you could use something like this:

Private Sub worksheet_change(ByVal target As Range)
Dim findrow As Integer
If Not Intersect(target, Range("E2")) Is Nothing Then
   findrow = Range("A1:A100").Find(what:=Range("E2").Value).Row
   Range("A" & findrow & ":B" & findrow).Delete
End If
End Sub

 

[evxret]

If you only want to delete column A and B values and shift the remainder up, you could use something like this:

Private Sub worksheet_change(ByVal target As Range)
Dim findrow As Integer
If Not Intersect(target, Range("E2")) Is Nothing Then
   findrow = Range("A1:A100").Find(what:=Range("E2").Value).Row
   Range("A" & findrow & ":B" & findrow).Delete
End If
End Sub

The bottom one is nearly perfect! One question though, this macro was written as a Worksheet_Change event, I don't want the event to be executed everytime the name is added, rather only when the button is pressed. (I plan on adding multiple buttons to accomplish different tasks based on the same cell). I know how to call Macros based on an ActiveX button press, Do I Just remove the Worksheet_Change code and move it to a module and call it from the button that way? Sorry, not super advanced with VBA and IDK if these questions are stupid.

 

[Candyman8019]

correct...change the name of the sub. You can keep it in the current sheet and simply call it from the button. If you do want to move it to a macro module then you'll need to specify a specific sheet that it is applicable to:

For example:

Private Sub deleteuser()
Dim findrow As Integer
   findrow = worksheets("Sheet1").Range("A1:A100").Find(what:=worksheets("Sheet1").Range("E2").Value).Row
   worksheets("Sheet1").Range("A" & findrow & ":B" & findrow).Delete
End Sub

 

[evxret]

correct...change the name of the sub. You can keep it in the current sheet and simply call it from the button. If you do want to move it to a macro module then you'll need to specify a specific sheet that it is applicable to:

For example:

Private Sub deleteuser()
Dim findrow As Integer
   findrow = worksheets("Sheet1").Range("A1:A100").Find(what:=worksheets("Sheet1").Range("E2").Value).Row
   worksheets("Sheet1").Range("A" & findrow & ":B" & findrow).Delete
End Sub

This worked perfectly!! I was totally overcomplicating it trying to do it myself.. You have taught me alot!
One more question for you, Would you be able to assist in a few more lines that will identify if the "Lookup" Cell is left blank or isn't found in the list, and displays a msgbox? Im not too versed on error handling and I cant get it to work without the sub failing all over.

 

[Candyman8019]

Try this:

Private Sub deleteuser()
Dim findrow As Integer
  On Error GoTo DispMess
   findrow = Range("A1:A100").Find(what:=Range("E2").Value).Row
   Range("A" & findrow & ":B" & findrow).Delete
   Exit Sub
DispMess:
 MsgBox "User Not Found"
End Sub

 

[evxret]

Try this:

Private Sub deleteuser()
Dim findrow As Integer
  On Error GoTo DispMess
   findrow = Range("A1:A100").Find(what:=Range("E2").Value).Row
   Range("A" & findrow & ":B" & findrow).Delete
   Exit Sub
DispMess:
 MsgBox "User Not Found"
End Sub

This code works; however the error msg is displayed even when the entry is found and deleted. Am I doing something wrong? I see that you have added the OnError & Exit Sub before the display msg, but it doesnt seem to be working for me...

 

[evxret]

Try this:

Private Sub deleteuser()
Dim findrow As Integer
  On Error GoTo DispMess
   findrow = Range("A1:A100").Find(what:=Range("E2").Value).Row
   Range("A" & findrow & ":B" & findrow).Delete
   Exit Sub
DispMess:
 MsgBox "User Not Found"
End Sub

Sub deleteuser()
Dim findrow As Integer
    
    ''Data Validation To Check If Data Entry Cell Was Left Blank
    If Range("G4").Value = True Then
    MsgBox "Data Entry Cell Was Left Blank.", vbCritical, "Error Modifying Roster"
    Exit Sub
    End If
    
    ''Locates Name In List & Deletes Contents
    On Error GoTo ErrMsg
    findrow = Worksheets("Employee Roster").Range("A1:A200").Find(what:=Worksheets("Employee Roster").Range("E3").Value).Row
    Application.ScreenUpdating = False
    Worksheets("Employee Roster").Range("A" & findrow & ":B" & findrow).Delete
    Worksheets("Employee Roster").Range("E3").ClearContents
    Application.ScreenUpdating = True
    Exit Sub

''Error Handling Msg
ErrMsg:
MsgBox "Employee Not Found.", vbCritical, "Error Modifying Roster"

End Sub

Heres a copy of the code I'm using after making my modifications. If that helps.

 

[Candyman8019]

Once it reaches the exit sub it shouldn't display the message box. Make sure the script is completely stopped and try again. I have tried the code and it does work as expected. If all else fails, restart excel and try it again.