How to delete data in Sheet 2 from Sheet1

[saints49]

Hi expert friends. I have a deleting problem. I hope someone can help me. Thanks in advance.

In Sheet1, I have a dropdown list. The list of data comes from Sheet2. I would want to select an item from the dropdown list to be deleted. That is, after selecting what I want from the list, I click a Delete button and the data in Sheet2 will be deleted automatically. What I have now is a Delete button which can only delete what I have selected from the list but will not delete the data in Sheet2. Can a macro be written to do this job? If possible, I would also like to have a "confirm" message box appearing after the user presses the Delete button before the data is really zapped away.
I have attached an example of the file so as to make my request clearer. Thanks.

 

[Debraj]

Hi saints49,

try this by updating your previous code..

Sub delete_B2()
  If Len(Range("B2")) = 0 Then
  MsgBox "  There is nothing to delete!", , "ALERT !"
  Else
  If MsgBox("Are you sure that you wish to delete the content?", vbYesNo, "CONFIRM") = vbYes Then
  Sheet2.Range("A" & Application.Match([b2], Sheet2.Range("A:A"), False)).EntireRow.Delete xlShiftUp
  [b2].ClearContents
  MsgBox "The content has been deleted !"
  End If
  End If
End Sub

 

[sgmpatnaik]

@deb

The Code works perfect, i have small doubt if we have two or more then it delete only first matched criteria

Thanks

 

[saints49]

Hi Debraj, thanks for your help. Your addition to the code works well for me.

 

[sgmpatnaik]

@saints

Try the below code if you want to delete the multiple results in the sheet2

Sub delete_B2()
Dim LR As Long, i As Long
 If Len(Range("B2")) = 0 Then
  MsgBox "  There is nothing to delete!", , "ALERT !"
  Else
 If MsgBox("  Are you sure that you wish to delete the content?", vbYesNo, "CONFIRM ") = vbYes Then
With Sheets("Sheet2")
  LR = .Range("A" & Rows.Count).End(xlUp).Row
  For i = LR To 1 Step -1
  If IsNumeric(Application.Match(.Range("A" & i).Value, Sheets("Sheet1").Range("B2"), 0)) Then .Rows(i).Delete
   
  Next i
   
End With
MsgBox "  The content has been deleted !"
Range("B2").ClearContents
End If
End If
End Sub

Thanks

 

[Debraj]

@ patnaik..
be prepared for the next query from OP...

How to create Dropdown..
using Data validation... From another sheet..
With unique item... and Dynamic...
He he he...
Happy Weekend..

 

[saints49]

Thanks sgmpatnaik, for the additional code. I'll try and test that too.

 

[sgmpatnaik]

@deb

sure bro, i have learned a lot of thing from this Group and you too

if any problem i will knock your door

He he he...

how was your week end, mine good