Excel VBA delete row based on cell

Question

I have this code, which I want to loop through all sheets and delete entire rows based on the value "completed" located anywhere in the sheet...This runs but only on the active sheet, I cant figure a good way to have it loop through every sheet till done...any help is greatly appreciated! ty

Private Sub TestDeleteRows()
Dim rFind As Range
Dim rDelete As Range
Dim strSearch As String
Dim sFirstAddress As String

strSearch = "Completed"
Set rDelete = Nothing

Application.ScreenUpdating = False

With Sheet1.Columns("A:AO")
Set rFind = .Find(strSearch, 
LookIn:=xlValues, LookAt:=xlPart,   SearchDirection:=xlNext, MatchCase:=False)
If Not rFind Is Nothing Then
    sFirstAddress = rFind.Address
    Do
        If rDelete Is Nothing Then
            Set rDelete = rFind
        Else
            Set rDelete = Application.Union(rDelete, rFind)
        End If
        Set rFind = .FindNext(rFind)
    Loop While Not rFind Is Nothing And rFind.Address <> sFirstAddress

    rDelete.EntireRow.Delete

End If
End With
Application.ScreenUpdating = False
End Sub

Can this be altered to do the following: Delete a row based on a column name and cell value? or multiple column names and cells? the example being: i want to delete anything that has a value of 'Complete' in column named 'Status' but I may also want to delete anything 'Complete' in Column named 'Second Status' as well..ty — Defca Trick
– Defca Trick, Commented Mar 14, 2017 at 20:14

dbodell · Accepted Answer · 2017-03-14 02:22:09Z

2

Private Sub TestDeleteRows()
Dim rFind As Range
Dim rDelete As Range
Dim strSearch As String
Dim sFirstAddress As String
Dim sh As Worksheet

strSearch = "Completed"
Set rDelete = Nothing

Application.ScreenUpdating = False
For Each sh In ThisWorkbook.Sheets
With sh.Columns("A:AO")
Set rFind = .Find(strSearch, LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=False)
If Not rFind Is Nothing Then
    sFirstAddress = rFind.Address
    Do
        If rDelete Is Nothing Then
            Set rDelete = rFind
        Else
            Set rDelete = Application.Union(rDelete, rFind)
        End If
        Set rFind = .FindNext(rFind)
    Loop While Not rFind Is Nothing And rFind.Address <> sFirstAddress

    rDelete.EntireRow.Delete
    Set rDelete = Nothing
End If
End With
Next sh
Application.ScreenUpdating = False
End Sub

edited Mar 14, 2017 at 2:22

answered Mar 14, 2017 at 2:03

dbodell

614 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Defca Trick Over a year ago

Hello, This has given the error "Compile Error:" For Each may only iterate over a collection object or an array" What happened?

dbodell Over a year ago

Sorry, can you try the edited code? I was using the count method but removed that so that its only accessing the sheet collection itself. Sorry about that.

Defca Trick Over a year ago

Sorry to be Trouble now it says "Runtime Error 1004: Method "Union of object _Application Failed" for the line under else..but still seems to work on sheet 1

dbodell Over a year ago

okay, perhaps we need to reset rDelete to nothing since the if statement doesn't evaluate to True if you're using multiple sheets and it runs at least once. Try the edited version and let me know if that works for you.

Defca Trick Over a year ago

Hello, I think you were right! it now works with no error! ty so much ^^

|

Collectives™ on Stack Overflow

Excel VBA delete row based on cell

1 Answer 1

13 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related