How to delete rows when a column does not start with certain numbers?

[powerpivot beginner]

Hi, I have a big set of trade stats of more than 50000 lines.
I want to keep only certain lines when column B starts with certain numbers.
For the below that I found online, it will only keep lines starting with 29.
I need to keep lines if column B has numbers starting with 292, 293 and 350. Please advise

Sub DeleteRowsNotStartingWith29()
  Application.ScreenUpdating = False 
  Columns("B").Replace "29", "=29", xlPart
  On Error Resume Next   
  Columns("B").SpecialCells(xlConstants).EntireRow.Delete
  On Error GoTo 0     
  Columns("B").Replace "=29", "29", xlPart
  Application.ScreenUpdating = True 
End Sub

 

[PCosta87]

Hi @powerpivot beginner

Maybe something like this (I am assuming you have headers so range would start in line 2):

Sub deleterows()

    Dim lastrow, i As Integer
   
    lastrow = Columns(2).Cells(Rows.Count).End(xlUp).Row
   
    For i = 2 To lastrow
        If Left(Cells(i, 2), 3) <> 292 And Left(Cells(i, 2), 3) <> 293 And Left(Cells(i, 2), 3) <> 350 Then
            Cells(i, 2).EntireRow.Delete
        End If
    Next

End Sub

Hope this helps

 

[jindon]

Change to

Sub DeleteRowsNotStartingWith29()
    Dim x, e
    Application.ScreenUpdating = False
    x = Array("292", "293", "350")
    For Each e In x
        Columns("B").Replace e, "=" & e, xlPart
    Next
    On Error Resume Next
    Columns("B").SpecialCells(xlConstants).EntireRow.Delete
    On Error GoTo 0
    For Each e In x
        Columns("B").Replace "=" & e, e, xlPart
    Next
    Application.ScreenUpdating = True
End Sub

 

[powerpivot beginner]

Thanks everyone for reply.

What if instead of deleting rows if a column does not starting with certain numbers, I want to delete rows if a column does not contain certain words, how should I revise the code? I am only keeping the row if
column E contain words starting with glu or citi. Thanks

Sub DeleteRowsNotStartingWith29()
  Dim x, e
    Application.ScreenUpdating = False
    x = Array("292", "293", "350")
  ForEach e In x
        Columns("B").Replace e, "=" & e, xlPart
  Next
  OnErrorResumeNext
    Columns("B").SpecialCells(xlConstants).EntireRow.Delete
  OnErrorGoTo 0
  ForEach e In x
        Columns("B").Replace "=" & e, e, xlPart
  Next
    Application.ScreenUpdating = True
EndSub

 

[jindon]

Try

Sub DeleteRowsNotStartingWith29()
    Dim x, e
    Application.ScreenUpdating = False
    x = Array("city", "glu", "292", "293", "350") '<- alter to suite
    For Each e In x
        If IsNumeric(e) Then
            Columns("E").Replace e, "=" & e, xlPart
        Else
            Columns("E").Replace e, "=""" & e & """&", xlPart
        End If
    Next
    On Error Resume Next
    Columns("B").SpecialCells(xlConstants).EntireRow.Delete
    On Error GoTo 0
    For Each e In x
        If IsNumeric(e) Then
            Columns("E").Replace "=" & e, e, xlPart
        Else
            Columns("E").Replace "=""" & e & """&", e, xlPart
        End If
    Next
    Application.ScreenUpdating = True
End Sub

 

[powerpivot beginner]

When I tried the below, all the rows got deleted.Just to clarify, I just need to
work on Column E. If Column E contains any words starting with citi or glu, the lines should remain. Likewise, all the rows should be deleted so I will be left with rows where there the keywords I am looking for is in Column E.

Sub DeleteRowsNotStartingWith29()
  Dim x, e
    Application.ScreenUpdating = False
    x = Array("city", "glu", "292", "293", "350") '<- alter to suite  ForEach e In x
      If IsNumeric(e) Then
            Columns("E").Replace e, "=" & e, xlPart
      Else
            Columns("E").Replace e, "=""" & e & """&", xlPart
      EndIf
  Next
  OnErrorResumeNext
    Columns("B").SpecialCells(xlConstants).EntireRow.Delete
  OnErrorGoTo 0
  ForEach e In x
      If IsNumeric(e) Then
            Columns("E").Replace "=" & e, e, xlPart
      Else
            Columns("E").Replace "=""" & e & """&", e, xlPart
      EndIf
  Next
    Application.ScreenUpdating = True
EndSub

 

[jindon]

Then I need to see your workbook.
It seems working here.

 

[powerpivot beginner]

Thanks for your help. I am attaching a sample. These are public exports stats of a certain country. I am looking for records of certain items ( but more than 50000 records per month).If in column E, it contain certain items containing certain words, eg citi or glu, then I want to review them. If not, they can be deleted. And I hope to keep the header row 1.

 

[jindon]

OK, 50000 rows... AdvncedFilter would be the best

Sub DeleteRowsNotStartingWith29()
    Dim rng As Range, x
    Application.ScreenUpdating = False
    x = Array("citi", "glu")
    With Cells(1).CurrentRegion
        Set rng = .Offset(, .Columns.Count + 2).Cells(1).Resize(2, UBound(x) + 1)
        .Range("e1").Copy rng(1).Resize(, UBound(x) + 1)
        rng.Rows(2).Value = x
        rng.Rows(2).Value = Evaluate("""<>""&" & rng.Rows(2).Address & "&""*""")
        .AdvancedFilter 1, rng
        .Offset(1).EntireRow.Delete
        On Error Resume Next
        .Parent.ShowAllData
        On Error GoTo 0
    End With
    Application.ScreenUpdating = True
End Sub

 

[powerpivot beginner]

Thanks for your help. But I tried it. It will only keep the lines starting with "citi" and "glu". Sometimes the words starting with "citi" and "glu" are in the middle of the goods description and the code do not effectively capture them. Anyway to change words starting with "citi" and "glu" in Column E to words containing "citi" and "glu" in Column E?

 

[jindon]

Then change

rng.Rows(2).Value = Evaluate("""<>""&" & rng.Rows(2).Address & "&""*""")

to

rng.Rows(2).Value = Evaluate("""<>*""&" & rng.Rows(2).Address & "&""*""")

 

[powerpivot beginner]

Dear Jindon

Problem solved. Thanks so much for your help and time