PDA

View Full Version : Macro to Clear Rows Not Containing a Particular Word/Text Across Worksheets



scottjkolb
10-02-2013, 06:27 AM
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
10-03-2013, 12:21 AM
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
10-03-2013, 02:54 AM
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