Delete All Rows Containing Value of Selected Cell

[marshalgaddis]

Hi guys,

I wanted a way to be able to delete all rows that contain a given value. I would like to be able to change the value easily, so I wanted to make a macro that would read my selected cell (into which I can just input what I want deleted from each row before running the macro) and delete every row that contains that value.

Any ideas?

Thanks a lot!

 

[taurean]

Does this value occur in a specific column or it could be anywhere in the row?

 

[marshalgaddis]

Anywhere in the row.

 

[taurean]

Test the following code:
1. Have a backup sheet handy (Just in case).
Note:
1. The code will retain Target.Row (Your Entry)
2. It will delete even if the sub-string matches i.e. If you are deleting "abc" and a cell contains "abcd" then it will delete. If you do not want that to happen then change "xlPart" to "xlWhole"
3. This is worksheet event code so Right Click on the Worksheet Tab and then choose "View Code" and paste code in the VBE window that appears:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, rDel As Range
Dim rAddress As String
Application.EnableEvents = False
Application.ScreenUpdating = False
On Error GoTo ResetDefaults
If Target.Address = "$A$1" Then 'Change your cell reference here!
Set r = ActiveSheet.UsedRange.Find(What:=Range("A1").Value, _
After:=Range("B1"), Lookat:=xlPart) 'Set After Value to next row of Target value
'e.g. Now Target is Cell A1, After is B1
'So if you change it to say C1 then change After to D1
    
    If Not r Is Nothing Then
    rAddress = r.Address
        Do
        Set rDel = r
        Set r = ActiveSheet.UsedRange.FindNext(r)
        rDel.EntireRow.Delete
        Loop While Not r Is Nothing And r.Row <> Target.Row
    Else
    MsgBox "The search keyword does not exist in the current sheet!"
    End If
End If
ResetDefaults:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[marshalgaddis]

This works exactly like I wanted except I would like to be able to have it as a stand-alone macro and run it on random worksheets that I come across (instead of having to go in and change the code of the worksheet, which will definitely lead me to accidentally delete important info).

Can this be made into a stand-alone macro??

 

[taurean]

This will let you pick up a cell of your choice through inputbox and the macro is standalone. Insert a module in the VBE window and paste this code in it.

Public Sub DelMatchRows()
Dim r As Range, rDel As Range, rToDel As Range
Dim rAddress As String
On Error GoTo ResetDefaults
Set rToDel = Application.InputBox("Please select Range with deletion criterion!", Type:=8)
Application.EnableEvents = False
Application.ScreenUpdating = False
Set r = ActiveSheet.UsedRange.Find(What:=rToDel.Value, After:=rToDel, _
Lookat:=xlPart) 'Set After Value to next row of Target value
'e.g. Now Target is Cell A1, After is B1
'So if you change it to say C1 then change After to D1
 
    If Not r Is Nothing Then
    rAddress = r.Address
        Do
        Set rDel = r
        Set r = ActiveSheet.UsedRange.FindNext(r)
        rDel.EntireRow.Delete
        Loop While Not r Is Nothing And r.Row <> rToDel.Row
    Else
    MsgBox "The search keyword does not exist in the current sheet!"
    End If
ResetDefaults:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[marshalgaddis]

So. Perfect.

Thank you very much!

 

[marshalgaddis]

Wait, I'm sorry, I do have one more question:

Would if be possible for the cell selected in inputbox to indicate to the macro the column in which the macro should look to find rows that need deleting?

 

[taurean]

Wait, I'm sorry, I do have one more question:

Would if be possible for the cell selected in inputbox to indicate to the macro the column in which the macro should look to find rows that need deleting?

Do you want to select a specific column through 'one more' inputbox?