VBA delete loop only deletes one line

%3CLINGO-SUB%20id%3D%22lingo-sub-2653190%22%20slang%3D%22en-US%22%3EVBA%20delete%20loop%20only%20deletes%20one%20line%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2653190%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI'm%20trying%20to%20create%20a%20macro%20that%20deletes%20all%20lines%20in%20a%20workbook%20having%20specific%20values%2C%20here%20is%20my%20code%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20DeleteRowswithSpecificValue()%0A%20%0ADim%20cell%20As%20Range%0A%20%0AFor%20Each%20cell%20In%20Range(%22x2%3Ax2000%22)%0A%20%20%20%20If%20cell.Value%20%3D%20%22defer%22%20Then%0A%20%20%20%20%20%20%20%20cell.EntireRow.Delete%0A%20%20%20%20End%20If%0ANext%20cell%0A%20%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20macro%20serves%20only%20to%20delete%20the%20first%20row%20containing%20the%20value%20not%20every%20row.%26nbsp%3B%20I'll%20attach%20the%20file%20I%20want%20it%20to%20work%20on.%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2653190%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2653565%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20delete%20loop%20only%20deletes%20one%20line%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2653565%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1129669%22%20target%3D%22_blank%22%3E%40ALASKANBULLWORM%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EIf%20you%20don't%20necessarily%20want%20to%20do%20it%20with%20VBA%2C%20an%20alternative%20with%20a%20filter%20could%20also%20help.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EExample%20in%20the%20inserted%20file.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20any%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20Community%20members%20reading%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

I'm trying to create a macro that deletes all lines in a workbook having specific values, here is my code:

 

 

Sub DeleteRowswithSpecificValue()
 
Dim cell As Range
 
For Each cell In Range("x2:x2000")
    If cell.Value = "defer" Then
        cell.EntireRow.Delete
    End If
Next cell
 
End Sub

 

 

The macro serves only to delete the first row containing the value not every row.  I'll attach the file I want it to work on.

Thanks in advance!

2 Replies

@ALASKANBULLWORM 

 

If you don't necessarily want to do it with VBA, an alternative with a filter could also help.

 

Example in the inserted file.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.

@NikolinoDE
Thank you for your reply. I am aware of this option, I am trying to create a large series of these macro functions to transform these data files regularly, more quickly, and for individuals I work with who have less knowledge of excel.