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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.