View original (French)

Excel VBA: delete rows not containing..

Solved
Eaheru Posted messages 205 Status Member -  
Eaheru Posted messages 205 Status Member -
Hello,

I'm writing a VBA macro designed to delete rows that do not contain certain characters in a given column.
The goal is to scan the rows in column "BI" from row 3000 to row 2
and if the value there does not include the following character strings: "PROD" or "REC", I delete the row.

I'm currently at this macro:
Sub suppression()

Dim z$, i&, k&
k = Cells(3000, 61).Row
For i = k To 2 Step -1
z = Cells(i, 1).Value
If Not (Cells(i, 61) Like "*PROD*" Or Cells(i, 61) Like "*REC*") Then Rows(i).Delete
Next
ActiveWorkbook.Save
End Sub

And there, it doesn't work. The macro works perfectly well if I only have one of the two reference values (PROD or REC*) but I don't see how to ask for multiple references (the terms in the macro here in bold are causing it to crash).
I'm getting the message "syntax error"

Does anyone know how to fix this macro please?
Thank you in advance for your help.

7 answers

  1.
    lermite222 Posted messages 9042 Status Contributor 1 199
     
    That's good, but you didn't take into account Bacus13's remark...
    and for Option Compare Text, it's AT THE VERY TOP OF THE MODULE
    And since you didn't include Option Explicit, you don't see the declaration error...
    z IS NOT EQUAL to z$
    to use z$, you must write z$ = Cells(i, 61).Value
    or you can dim z as String, then z = Cells(i, 61).Value, that's fine
    Also, get into the habit of putting Option Explicit in all your modules.
    Easier... in the VBA editor >> Tools >> Options >> check >> Required variable declaration 
    Option Explicit Option Compare Text Sub suppression() Dim z as String For i = 3000 To 2 Step -1 z = Cells(i, 61).Value If Not (Cells(i, 61) Like ("PROD*") Or Cells(i, 61) Like ("REC*")) Then Rows(i).Delete Next ActiveWorkbook.Save End Sub

    --
    Experience teaches more surely than advice. (André Gide)
    If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-)(Confucius)
    1
  2. lermite222 Posted messages 9042 Status Contributor 1 199
     
    Hello,
    Where can the PROD or REC expressions be found in the cell label?
    A+
    Edit:
    You need to add Option Compare Text at the top of the module if you haven't done so.
    And your formula should be... 
    If Not (Cells(i, 61) Like ("PROD*") Or Cells(i, 61) Like ("REC*")) Then Rows(i).Delete


    Experience teaches more surely than advice. (André Gide)
    If you bump into a pot and it sounds hollow, it doesn’t necessarily mean that the pot is empty. ;-)(Confucius)
    0
  3. Eaheru Posted messages 205 Status Member 20
     

    PROD
    RECON
    RECIA
    0
    1. lermite222 Posted messages 9042 Status Contributor 1 199
       
      see modification of previous post
      0
  4. bacus13 Posted messages 30 Status Member 8
     
    nothing to do with it but why do k = Cells(3000, 61).Row and not k = 3000?
    or even For i = 3000 to 2 Step -1?
    0
  6. Eaheru Posted messages 205 Status Member 20
     
    Hello and thank you for this response.
    So it works perfectly, although I couldn't add the option: "Option Compare Text" in my macro;
    I receive the return: "Incorrect instruction in a procedure"

    Despite this detail, the routine works very well as follows:

    Sub suppression()

    Dim z$, i&, k&
    k = Cells(3000, 61).Row
    For i = k To 2 Step -1
    z = Cells(i, 61).Value
    If Not (Cells(i, 61) Like ("PROD*") Or Cells(i, 61) Like ("REC*")) Then Rows(i).Delete
    Next
    ActiveWorkbook.Save
    End Sub

    Thank you for your help :)
    0
  7. Eaheru Posted messages 205 Status Member 20
     
    Ah ok, I see the mistake.
    I will fix that first thing tomorrow morning.

    Thanks to you and Bacus (Sorry, I didn't see Bacus's message).
    Your help has been invaluable to me! :)
    0
  8. Eaheru Posted messages 205 Status Member 20
     
    Corrected macro taking your suggestions into account.
    It works perfectly!
    0