How to make Macro that prompts for user input and deletes a row that contains that value?

Posted by david on December 21, 2001 9:53 AM

Hi,
I need to create a macro that prompts the user to enter in a value, search the spreadsheet cells for that value, if it finds it, highlight the entire row and delete it from the spreadsheet.

I've been able to do this on my own, but only by manually entering in the value and then manually selecting the row and deleting it.

I just need to make the next step and automate this process. I hope there is an easy way to do this?

Thanks,
David

Posted by Ivan F Moala on December 21, 2001 12:35 PM

Try something like this;
Note: deletes Rows with Numbers ONLY ??

Sub FindValue_DeleteRow()
Dim Val As Range
Dim fWhat
Dim oCell As Range

fWhat = Application.InputBox("Enter Value to find", Type:=1)
If fWhat = False Then Exit Sub

Set Val = Cells.SpecialCells(2, 1)

On Error Resume Next
Do
Set oCell = Val.Find(What:=fWhat, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
oCell.EntireRow.Delete
Loop Until Err &LT;&GT; 0

End Sub

HTH

Ivan

Posted by Damon Ostrander on December 21, 2001 12:52 PM

Hi David,

This macro handles either text or numbers.

Sub FindDeleteRows()

' This macro deletes all rows having cells containing the
' specified input value (this value is prompted for).

Dim FindVal As String
Dim FoundCell As Range

Again:
FindVal = InputBox("Enter value to find", "Delete Rows Containing...")

If FindVal = "" Then Exit Sub

Set FoundCell = ActiveSheet.UsedRange.Find(FindVal, LookIn:=xlValues, _
lookat:=xlWhole, searchorder:=xlByRows)

Do While Not FoundCell Is Nothing

FoundCell.EntireRow.Select
If MsgBox("Delete this row", vbYesNo + vbQuestion, _
"Verify Row Deletion") = vbYes Then
FoundCell.EntireRow.Delete
Set FoundCell = ActiveSheet.UsedRange.FindNext
End If

Loop

If MsgBox("Another search?", vbYesNo + vbQuestion, _
"Not Found") = vbYes Then GoTo Again

End Sub

Happy computing.

Damon