View original (French)

Macro to delete lines that do not contain a given term

Solved
ClemG -  
 ClemG -
Hello everyone!

As a beginner in macros, I have a small problem: I have a list of products with different flavors in Excel (example: cranberry, cranberry & raspberry; orange; apple).
If the cells in column D (where these products are located) do not contain the terms "cranberry" or "pomegranate", then I want to delete the corresponding rows.

In other words, I only want to keep in my Excel sheet the products containing cranberry or pomegranate.

I have so far tried with this macro:

Sub super_fruits()
'
For Each cell In Range("D4:D450")
If Not cell.Value Like "Cranberry" Or cell.Value Like "Pomegranate" Then Rows(cell.Row).Delete
Next
Dim I As Integer
For I = 450 To 4 Step -1
If Not Cells(I, 4) Like "Cranberry" Then
Rows(I).Delete
End If
Next

End Sub

And now I encounter 2 problems: firstly, my macro only keeps the lines containing exclusively "Cranberry" (not taking into account "Pomegranate"). Secondly, I do not want to keep only the lines containing "cranberry" but also those like "cranberry & raspberry", for example, where cranberry is included.

I hope you can help !!!! thanks in advance for all your tips
Clem

2 answers

  1.
    pilas31 Posted messages 1878 Status Contributor 648
     
    Hello,

    To try:

    Sub super_fruits() Dim I As Integer For I = 450 To 4 Step -1 If Not (Cells(I, 4).Value Like "*Cranberry*" Or Cells(I, 4).Value Like "*Pomegranate*") Then Rows(I).Delete End If Next End Sub

    See you,
    Best regards,
    1
  2. ClemG
     
    It works... a big thank you!!!!!!!!

    See you soon!
    0