Delete Rows where Left()

[gheyman]

I need to delete each row if the content for the first 5 left characters = "10-TX"
Down Column M I have data that looks like 10-FL01-25 or 10-TX-04-028 or 10-TX-03-02 or 10-FL02-01....

I want to delete the Rows that either have "TX" (Or if that's too difficult then delete where the left 5 characters equal "10-TX")

Not sure how to modify this to do that.

Sub DeleteIfTexasPart()
'Author: G Heyman 191111
'Delete Rows where Comp_Type is not "Statndard"
'I is where the Comp Type is
Dim LR As Long, l As Long
LR = Range("M" & Rows.Count).End(xlUp).Row
For Z = LR To 2 Step -1
If Range("M" & Z).Value = XXXXXX Then Rows(Z).Delete
Next Z
End Sub

 

[Fluff]

Do you want to delete all rows where col M contains TX anywhere in the cell?

 

[shg]

Sub gh()
  Dim cell          As Range

  Do
    Set cell = Range("M:M").Find(what:="10-TX*", LookIn:=xlValues, LookAt:=xlWhole)
    If cell Is Nothing Then Exit Do
    cell.EntireRow.Delete
  Loop
End Sub

 

[DataBlake]

You can use arrays and case statement instead which should be much faster than the method you're attempting to use.
This can allow you to use multiple search criteria. Say you want to get rid of 10-TX, 10-DX, and 3-DD (note: this looks up the entirety of the cell and not just the first 5 characters)

Sub duallieFILTER()
Dim ary1 As Variant, ary2 As Variant
Dim os As Worksheet
Dim i As Long, j As Long, x As Long

Set os = Worksheets("Sheet1")
ary1 = os.Range("A1").CurrentRegion.Value2
ReDim ary2(1 To UBound(ary1), 1 To 1)

x = Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
For i = 2 To UBound(ary1)
    If Not IsError(ary1(i, 7)) Then
      Select Case True
        Case InStr(1, ary1(i, 7), "10-TX", 1) > 0, _
             InStr(1, ary1(i, 3), "10-DX", 1) > 0, _
             InStr(1, ary1(i, 3), "3-DD", 1) > 0
            
          ary2(i, 1) = 1
          j = j + 1
          
          
      End Select
    End If
Next i

 If j > 0 Then
    Application.ScreenUpdating = False
    With os.Range("A1").Resize(UBound(ary1), x)
      .Columns(x).Value = ary2
      .Sort Key1:=.Columns(x), Order1:=xlAscending, Header:=xlNo, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
      .Resize(j).EntireRow.Delete
    End With
    Application.ScreenUpdating = True
  End If

End Sub

this is just what i use to filter phrases or part numbers from my data

 

[gheyman]

Do you want to delete all rows where col M contains TX anywhere in the cell?

yes

 

[Fluff]

In that case, another way

Sub gheyman()
    With ActiveSheet
        .Range("A1:M1").AutoFilter , 13, "*TX*"
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .AutoFilterMode = False
    End With
End Sub

 

[gheyman]

In that case, another way

Sub gheyman()
    With ActiveSheet
        .Range("A1:M1").AutoFilter , 13, "*TX*"
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .AutoFilterMode = False
    End With
End Sub

What if I wanted to do the opposite? Delete if it doesn't contain "TX".
Or more specifically, I'm trying to delete rows (different workbook) if the value doesn't start with an "X" in column A (Sheets("Archive")

Thanks

 

[Fluff]

Just use "<>X*"as the criteria

 

[gheyman]

THNAK YOU!

 

[Fluff]

You're welcome & thanks for the feedback.