delete row with specific text entered from input box

[thomasks]

I have a spreadsheet that I need to find rows with values entered by an input box. If the value is found, then delete the row.
I want to be able to enter wildcards so that any value containing the text will result in deletion of the row.
I used snippets I found on the web to get started, but I can't get the wildcard input to work. And once all the rows are deleted the VBA returns a 91 error saying object or with variable not set.
Any help is appreciated.

<code>
Sub Find_and_Delete()
Dim LastRow As Integer
Dim rng As Range
Dim InputRng As Range
Dim DeleteRng As Range
Dim DeleteStr As String
Dim FirstCell As Range, LastCell As Range
Set LastCell = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Column)
Set FirstCell = Cells(Cells.Find(What:="*", After:=LastCell, _
SearchOrder:=xlRows, SearchDirection:=xlNext, LookIn:=xlValues).Row, _
Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, LookIn:=xlValues).Column)

Set InputRng = Range(FirstCell, LastCell)
DeleteStr = Application.InputBox("Delete Text", xTitleId)
For Each rng In InputRng
If rng.Value = DeleteStr Then
If DeleteRng Is Nothing Then
Set DeleteRng = rng
Else
Set DeleteRng = Application.Union(DeleteRng, rng)
End If
End If
Next
DeleteRng.EntireRow.Delete
End Sub


<code\>

 

[PHV]

Replace this:
If rng.Value = DeleteStr Then
with this:
If rng.Value Like DeleteStr Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

thomasks,
It's fairly easy to select your code block and then click the code icon to achieve proper TGML formatting. Note, I didn't change the code as suggested by PHV.

Sub Find_and_Delete()
    Dim LastRow As Integer
    Dim rng As Range
    Dim InputRng As Range
    Dim DeleteRng As Range
    Dim DeleteStr As String
    Dim FirstCell As Range, LastCell As Range
    Set LastCell = Cells(Cells.Find(What:="*", SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
        Cells.Find(What:="*", SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious, LookIn:=xlValues).Column)
    Set FirstCell = Cells(Cells.Find(What:="*", After:=LastCell, _
        SearchOrder:=xlRows, SearchDirection:=xlNext, LookIn:=xlValues).Row, _
        Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, LookIn:=xlValues).Column)
    Set InputRng = Range(FirstCell, LastCell)
    DeleteStr = Application.InputBox("Delete Text", xTitleId)
    For Each rng In InputRng
        If rng.Value = DeleteStr Then
            If DeleteRng Is Nothing Then
                Set DeleteRng = rng
             Else
                Set DeleteRng = Application.Union(DeleteRng, rng)
            End If
        End If
    Next
    DeleteRng.EntireRow.Delete
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

So if the user types * in the input box, will you delete all rows?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.