VBA to find certain text and then delete whole rows containing it in all sheets of a workbook

nats2412

New Member
Joined
Apr 19, 2017
Messages
3
Hi,

I haven't written/recorded a macro in a very very long time and am struggling to try and do something simple.

I need to search all worksheets of a file (containing around 100 tabs) for cells which contain the text "_web.pdf". There is also other text in the cells. Then for all cells which contain this specific text, I would like to delete the entire row. As there are over 100 sheets in the file, I'm trying to write a macro that will do all this automatically.

Is it possible? Can anyone help me please?

Thanks :)
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Hi,

Found this code on a different website, except for the looping through worksheets:

Code:
Sub TestDeleteRows()Dim rFind As Range
Dim rDelete As Range
Dim strSearch As String
Dim iLookAt As Long
Dim bMatchCase As Boolean
Dim WS As Worksheet


strSearch = "_web.pdf"    'Value to search for inbetween ""


iLookAt = xlPart 'Change to xlWhole if the entire cell must equal search string
bMatchCase = False  'Change to True if you want search to be case sensitive


Set rDelete = Nothing


Application.ScreenUpdating = False


For Each WS In Worksheets
With WS.UsedRange
    Set rFind = .Find(strSearch, LookIn:=xlValues, LookAt:=iLookAt, SearchDirection:=xlPrevious, MatchCase:=bMatchCase)
    If Not rFind Is Nothing Then
        Do
            Set rDelete = rFind
            Set rFind = .FindPrevious(rFind)
            If rFind.Address = rDelete.Address Then Set rFind = Nothing
            rDelete.EntireRow.Delete
        Loop While Not rFind Is Nothing
    End If
End With
Next
Application.ScreenUpdating = True
End Sub

Does this help at all?

Best Regards,
Mart
 
Upvote 0
Here is another macro for you to consider...
Code:
[table="width: 500"]
[tr]
	[td]Sub DeleteRows()
  Dim WS As Worksheet
  On Error Resume Next
  For Each WS In Sheets
    With WS.UsedRange
      .Replace "*_web.pdf*", "#N/A", xlWhole, , False
      Intersect(.Cells, .SpecialCells(xlConstants, xlErrors).EntireRow).Delete
    End With
  Next
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
Hi,

Found this code on a different website, except for the looping through worksheets:

Code:
Sub TestDeleteRows()Dim rFind As Range
Dim rDelete As Range
Dim strSearch As String
Dim iLookAt As Long
Dim bMatchCase As Boolean
Dim WS As Worksheet


strSearch = "_web.pdf"    'Value to search for inbetween ""


iLookAt = xlPart 'Change to xlWhole if the entire cell must equal search string
bMatchCase = False  'Change to True if you want search to be case sensitive


Set rDelete = Nothing


Application.ScreenUpdating = False


For Each WS In Worksheets
With WS.UsedRange
    Set rFind = .Find(strSearch, LookIn:=xlValues, LookAt:=iLookAt, SearchDirection:=xlPrevious, MatchCase:=bMatchCase)
    If Not rFind Is Nothing Then
        Do
            Set rDelete = rFind
            Set rFind = .FindPrevious(rFind)
            If rFind.Address = rDelete.Address Then Set rFind = Nothing
            rDelete.EntireRow.Delete
        Loop While Not rFind Is Nothing
    End If
End With
Next
Application.ScreenUpdating = True
End Sub

Does this help at all?

Best Regards,
Mart

Thank you so much, this works perfectly on one computer but not the other (older version of excel). Does it matter which version of excel the macro is used with?
 
Upvote 0
Here is another macro for you to consider...
Code:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub DeleteRows()
  Dim WS As Worksheet
  On Error Resume Next
  For Each WS In Sheets
    With WS.UsedRange
      .Replace "*_web.pdf*", "#N/A", xlWhole, , False
      Intersect(.Cells, .SpecialCells(xlConstants, xlErrors).EntireRow).Delete
    End With
  Next
End Sub
[/TD]
[/TR]
</tbody>[/TABLE]

Hi Rick, thank you so much. That seems to work like a dream. And so quick!!!! You're awesome! Have a great day!!!
 
Upvote 0
Please explain that one Rick.

Are you replacing web.pdf with an error and then deleting the entire row that has an #N/A error?
 
Upvote 0
Please explain that one Rick.

Are you replacing web.pdf with an error and then deleting the entire row that has an #N/A error?
Basically, yes, but the that error is a constant which distinguishes it from the #N/A error generated by a formula, so if there are any formula generated #N/A errors on a row without "_web.pdf" in it, they will remain... the idea being to convert the cells being sought after to something that the SpecialCells method can locate... the Intersect line handles the problem with trying to delete a row containing multiple, non-contiguous target cells.
 
Last edited:
Upvote 0
Rick, that is really impressive. So, is it the xlWhole that turns it into a constant?
You cannot do a direct xlPart replacement because that would only put #N/A in the middle of the text that the "_web.pdf" was in; rather, you need to tell Replace to do a wildcard search for "*_web.pdf*" and have it perform an xlWhole replacement (although in thinking about it now, I guess that particular wildcard search would work for xlPart as well)... the key is the two asterisk wildcards forcing a match of everything in the cell when "_web.pdf" is in the cell. The reason I like my Replace solution is that the Replace functionality in Excel is pretty much optimized and works quite quickly as does the SpecialCells method, which is VBA's implementation of Excel's GoTo dialog box (Ctrl+G or F5) followed by clicking its Special button.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top