asked on

Remove Rows based on criteria

Hello

I have two sheets in a workbook, I need to create a macro that will search both sheets and if *Load Balance* or "White Noise* is found, then delete that entire row and move the rows below it up.

Saurabh Singh Teotia

Quick question..Which column will have these both the values in the worksheet??

Saurabh...

sandramac

ASKER

The data can appear in any Column within A:L

ASKER CERTIFIED SOLUTION

Saurabh Singh Teotia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Subodh Tiwari (Neeraj)

Another approach is this...
Assuming row1 is the header row on each sheet. If not change the row number in line 8 With ws.Rows(1)

Sub DeleteRows()
Dim ws As Worksheet
Dim lr As Long
Application.ScreenUpdating = False
For Each ws In Worksheets
   lr = ws.UsedRange.Rows.Count
   ws.AutoFilterMode = 0
   With ws.Rows(1)
      For c = 1 To ws.UsedRange.Columns.Count
         .AutoFilter field:=c, Criteria1:="=*white noise*", Operator:=xlOr, Criteria2:="=*load balance*"
         If ws.Range("A1:A" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
            ws.Range("A2:A" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Delete
         End If
         .AutoFilter field:=c
      Next c
   End With
Next ws
Application.ScreenUpdating = True
End Sub

Open in new window

sandramac

ASKER

Thank You

Saurabh Singh Teotia

sandramac..You are welcome...Always happy to help.. :-)

Saurabh...