Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Certain Rows Based on User Input
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Certain Rows Based on User Input
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:D" & 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete rows based on date input | Excel Programming | |||
show a number of rows based upon user input | Excel Discussion (Misc queries) | |||
delete every row where value equals user input | Excel Programming | |||
Hiding rows based on user input | Excel Programming | |||
CODE to select range based on User Input or Value of Input Field | Excel Programming |