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

 

[Peter_SSs]

3.if column I has borle,haroldh,llavner,jlinares,ephriamw,mordi,moshw,rheins,toviag,jvelez,josfrcs delete the line

Will column I have all that text or do you mean any one of the words listed?
If it is any word, will that word be by itself in column I or could it be combined? eg "abc rheins def"

Similar with column B. Could a cell have "LEICA BELL" and if so should it get deleted?

In column I and B does the case have to match? That is, could "leica" exist in col B and if so does it get deleted?

About how may rows altogether in your data?

 

[Tonyk1051]

Will column I have all that text or do you mean any one of the words listed?
If it is any word, will that word be by itself in column I or could it be combined? eg "abc rheins def"

Similar with column B. Could a cell have "LEICA BELL" and if so should it get deleted?

In column I and B does the case have to match? That is, could "leica" exist in col B and if so does it get deleted?

About how may rows altogether in your data?

for column I its any of those key words thats found in any row, and column b for any row that has any type of text in it, the line should be deleted, total amount of lines for the worksheet is about 3000 lines

 

[Peter_SSs]

That does not answer either of these issues.

Will column I have all that text or do you mean any one of the words listed?
If it is any word, will that word be by itself in column I or could it be combined? eg "abc rheins def"

Similar with column B. Could a cell have "LEICA BELL" and if so should it get deleted?

 

[Tonyk1051]

That does not answer either of these issues.

report.xlsm

i think seeing it will help, let me know if it doesnt

 

[Tonyk1051]

report.xlsm

i think seeing it will help, let me know if it doesnt

tab 2

 

[Peter_SSs]

i think seeing it will help

I think answering the questions would help more (& save helpers having to look through thousands of lines of data)

I asked about if, for example, "LEICA" would be by itself or could be included with other words and if so whether that line should be deleted. I don't believe that a clear answer to that was given.

I notice in the sample file that one row at least contains "LEICA CAMERA INC." but I do not know if that line should be deleted or not.

If that row should be deleted then the logic could follow that the following rows should also be deleted because they all contain "ADI" which is included in your delete list for column B.
YORK TRADING INC.
RADIAL ENGINEERING LTD.
MIDLAND RADIO CORPORATION
etc

 

[Tonyk1051]

Will column I have all that text or do you mean any one of the words listed?
If it is any word, will that word be by itself in column I or could it be combined? eg "abc rheins def"

Similar with column B. Could a cell have "LEICA BELL" and if so should it get deleted?

In column I and B does the case have to match? That is, could "leica" exist in col B and if so does it get deleted?

About how may rows altogether in your data?

column I, its any one of the words and it will be by itself never combined.
column B, these are all company names, the case doesnt have to match, so long as one of those names appear the line must be deleted. does this answer help?

 

[Peter_SSs]

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

 

[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

Hi,

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