Excel vba question delete rows if contains certain text

I need help with a VBA solution to search every sheet in thisworkbook in column B and if the following values are found then delete entire row for that.

any row that its column B contain any of the below values then entire row to be deleted, the search to be done in all worksheets of thisworkbook.

thanks,. alot
B67047033
B32679596
B35979952
B32868901
B96033022
LVL 6
FloraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this....
Sub DeleteRows()
Dim ws As Worksheet
Dim lr As Long
Dim arr
lr = Cells(Rows.Count, "B").End(xlUp).Row
arr = Array("B67047033", "B32679596", "B35979952", "B32868901", "B96033022")

Application.ScreenUpdating = False
For Each ws In Worksheets
   ws.AutoFilterMode = 0
   With ws.Rows(1)
      .AutoFilter field:=2, Criteria1:=arr, Operator:=xlFilterValues
      If ws.Range("B1:B" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
         ws.Range("B2:B" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Delete
      End If
      .AutoFilter
   End With
Next ws
Application.ScreenUpdating = True
MsgBox "Rows have been deleted successfully.", vbInformation, "Done!"
End Sub

Open in new window

0
FloraAuthor Commented:
thank you very much sktneer

how do you alter this above code to look only for rows into the sheets with names

Sheets("ProductA")  sheets("ProductB")
0
FloraAuthor Commented:
i get error in line 12 .AutoFilter field:=2, Criteria1:=arr, Operator:=xlFilterValues

Run-time Error "1004"

the command copuld not be completed by using the range specified.

Select a single cell within the range and try command again
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Rgonzo1971Commented:
pls try first with the sheets name

Sub DeleteRows()
Dim ws As Worksheet
Dim lr As Long
Dim arr
lr = Cells(Rows.Count, "B").End(xlUp).Row
arr = Array("B67047033", "B32679596", "B35979952", "B32868901", "B96033022")
Application.ScreenUpdating = False
For Each ws In Worksheets
    If ws.Name = "ProductA" Or ws.Name = "ProductB" Then
        ws.AutoFilterMode = 0
        With ws.Rows(1)
           .AutoFilter field:=2, Criteria1:=arr, Operator:=xlFilterValues
           If ws.Range("B1:B" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
              ws.Range("B2:B" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Delete
           End If
           .AutoFilter
        End With
    End If
Next ws
Application.ScreenUpdating = True
MsgBox "Rows have been deleted successfully.", vbInformation, "Done!"
End Sub

Open in new window

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You stated in your original post that you want to run the code for each worksheet in the workbook.
Anyways the correction made by Rgonzo1971 would work without an issue.
0
FloraAuthor Commented:
thanks to both of you.



sktneer any idea why it was giving error in your first code?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.