Macro to Clear Rows Not Containing a Particular Word/Text Across Worksheets

[scottjkolb]

I have an Excel spreadsheet with over 100 worksheets. The data is in column B through AT, rows 11 through 4914. I need a macro that will clear (not delete) all rows that DO NOT have a particular word (i.e., Public) in column D across all worksheets. Thanks.

[EirikDaude]

I think this should do the trick. Not all that elegant, but meh :P

Option Explicit

Sub tester()
  Application.ScreenUpdating = False
  Dim w As Worksheet, r As Range, s As String
  
  s = "Public"
  
  For Each w In Worksheets
    For Each r In w.Range("D11:D4914")
      If Not InStr(r, s) Then
        Range(r.Offset(0, -2), r.Offset(0, 42)).Clear
      End If
    Next
  Next
  Application.ScreenUpdating = True
End Sub

[snb]

Sub M_snb()
  for each sh in sheets
    With sh.Range("D10:D4914")
        .AutoFilter 1, "<>Public"
        .Offset(1).SpecialCells(12).EntireRow.ClearContents
        .AutoFilter
    End With
  next
End Sub