Excel VBA: delete rows not containing..
Solved
Eaheru
Posted messages
205
Status
Membre
-
Eaheru Posted messages 205 Status Membre -
Eaheru Posted messages 205 Status Membre -
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.
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 réponses
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
--
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)
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)
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...
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)
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)
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?
or even For i = 3000 to 2 Step -1?
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 :)
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 :)