MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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

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?


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
Set oCell = Val.Find(What:=fWhat, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
Loop Until Err <> 0

End Sub



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

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

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


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

End Sub

Happy computing.