macro for deleting entire lines if a row has no text /certain text

[Tonyk1051]

Hi,


I found a basic macro from here that works below for steps 1 and 2 but i need to know what the code should look like when applying steps 3 through 5 (i tried tinkering around but got nothing) any help is appreciated

1.if column L is greater than 1 delete the line
2.if column F has Y delete the line
3.if column I has borle,haroldh,llavner,jlinares,ephriamw,mordi,moshw,rheins,toviag,jvelez,josfrcs delete the line
4.if column C has any text at all delete the line
5.if column B has any of the names listed below delete the line

OPTOMA
LEICA
MATIAS CORPORATION
DRACO
ROLAND SYSTEMS GROUP U.S.
Hal Leonard
ASUS COMPUTER INT'L
SHENZHEN LEQI NETWORK TECH.
YAMAHA CORPORATION OF AMERICA
SHURE INCORPORATED
HONG KONG YONG NUO PHOTOGRAPHI
PENGO TECHNOLGOY CO. LTD.
SEAGATE TECHNOLOGY, LLC
WESTERN DIGITAL
HAL LEONARD CORP,
TECH DATA CORP.
INGRAM MICRO
D & H DISTRIBUTING CO.
1 SOURCE VIDEO.
HITACHI GLOBAL STORAGE TECH.
BBQ TRADING LLC
JEG & SONS INC.
AMAZON.COM AUCTIONS
ADI
ACER AMERICA, INC
BOSE CORPORATION
ASI COMPUTER TECHNOLOGIES INC.
AUDIOENGINE, LTD.
PROMPTERPEOPLE
COREL
HIKVISION USA INC / REPAIR CEN
ORANGEMONKIE INC
SONOS INC

 Sub test()
With ActiveSheet
    .AutoFilterMode = False
    With Range("L1", Range("L" & Rows.Count).End(xlUp))
        .AutoFilter 1, ">1"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With
End Sub

 

[Tonyk1051]

Still no direct answer about "LEICA BELL" but give this a try with a copy of your workbook.

Sub Del_Rows()
  Dim a As Variant, b As Variant, aRws As Variant, CoNames As Variant
  Dim nc As Long, i As Long, k As Long
  Dim bDel As Boolean
 
  CoNames = Split("OPTOMA|LEICA|MATIAS CORPORATION|DRACO|ROLAND SYSTEMS GROUP U.S.|Hal Leonard|ASUS COMPUTER INT'L|SHENZHEN LEQI NETWORK TECH.|" _
                & "YAMAHA CORPORATION OF AMERICA|SHURE INCORPORATED|HONG KONG YONG NUO PHOTOGRAPHI|PENGO TECHNOLGOY CO. LTD.|SEAGATE TECHNOLOGY, LLC|" _
                & "WESTERN DIGITAL|HAL LEONARD CORP,|TECH DATA CORP.|INGRAM MICRO|D & H DISTRIBUTING CO.|1 SOURCE VIDEO.|HITACHI GLOBAL STORAGE TECH.|" _
                & "BBQ TRADING LLC|JEG & SONS INC.|AMAZON.COM AUCTIONS|ADI|ACER AMERICA, INC|BOSE CORPORATION|ASI COMPUTER TECHNOLOGIES INC.|" _
                & "AUDIOENGINE, LTD.|PROMPTERPEOPLE|COREL|HIKVISION USA INC / REPAIR CEN|ORANGEMONKIE INC|SONOS INC", "|")
 
  With Sheets("RO Level Summary")
    nc = Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
    aRws = Evaluate("row(2:" & .Range("B" & Rows.Count).End(xlUp).Row & ")")
    a = Application.Index(.Cells, aRws, Array(2, 3, 6, 9, 12))
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      bDel = False
      Select Case True
        Case a(i, 5) > 1: bDel = True
        Case a(i, 3) = "Y": bDel = True
        Case InStr(1, ",borle,haroldh,llavner,jlinares,ephriamw,mordi,moshw,rheins,toviag,jvelez,josfrcs,", "," & a(i, 4) & ",", vbTextCompare) > 0: bDel = True
        Case Len(a(i, 2)) > 0: bDel = True
        Case IsNumeric(Application.Match(a(i, 1), CoNames, 0))
          bDel = True
      End Select
      If bDel Then
        k = k + 1
        b(i, 1) = 1
      End If
    Next i
    If k > 0 Then
      Application.ScreenUpdating = False
      With Range("A2").Resize(UBound(a), nc)
        .Columns(nc).Value = b
        .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
        .Resize(k).EntireRow.Delete
      End With
      Application.ScreenUpdating = True
    End If
  End With
End Sub

Hey it works perfect, thank you so much

 

[Peter_SSs]

So basically this macro you created does all of steps 1 through 5?

Yes.

Hey it works perfect, thank you so much

You're welcome. Glad it worked for you.