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

[nats2412]

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

 

[Martvg]

Hi,

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

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

 

[Rick Rothstein]

Here is another macro for you to consider...

[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]

 

[nats2412]

Hi,

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

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?

 

[nats2412]

Here is another macro for you to consider...

[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!!!

 

[Sandler]

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?

 

[Rick Rothstein]

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.

 

[Sandler]

Rick, that is really impressive. So, is it the xlWhole that turns it into a constant?

 

[Rick Rothstein]

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.