Delete Certain Rows Based on User Input

[kmzito]

Hi everyone,

The below macro is the beginning half of what I'm working with.

What I need it to do is Prompt the user for a vendor name, find it on
the 'List" sheet, then delete all rows that contain the same values
(all rows that it needs to delete would be the same with the exception
being the the A column). I would just have it sort and automatically
delete like 5 or 10 but the issue is sometimes its 4, sometimes 1
sometimes 12, etc.


Dim sUsername As String
Dim sPrompt As String

sPrompt = "Please enter vendor name"
sUsername = InputBox(sPrompt, sTitle, sDefault)

Sheets("list").Select
Range("d4").Select
Cells.Find(What:=sUsername, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Select

Answer = MsgBox("Is this the contract/vendor you would like to
delete?", vbYesNo + vbInformation, "Please Confirm")
If Answer = vbYes Then
Selection.EntireRow.Delete

Exit Sub

 

[ryguy7272]

Sub SortDelete()
Dim sUsername As String
Dim sPrompt As String
sPrompt = "Please enter vendor name"
sUsername = InputBox(sPrompt, sTitle, sDefault)
Sheets("list").Select
Range("d4").Select
Cells.Find(What:=sUsername, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Select
Answer = MsgBox("Is this the contract/vendor you would like to delete?",
vbYesNo + vbInformation, "Please Confirm")
If Answer = vbYes Then

Dim myrange, MyRange1 As Range
LastRow = Cells(Rows.Count, "D").End(xlUp).Row
Set myrange = Sheets("list").Range("D4" & LastRow)
For Each C In myrange
If (C.Value) = sUsername Then
If MyRange1 Is Nothing Then
Set MyRange1 = C.EntireRow
Else
Set MyRange1 = Union(MyRange1, C.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If
End If
Exit Sub
End Sub

HTH,'
Ryan---