Do while with IF

[Barbu]

Hi,

I need to do a couple of do whiles with an IF that validate if a cell it's equal to a number (locations codes) then delete the entire row, (I need them in 2 different macros)
And the issue is that I have 90 locations codes (for now) So, how can I create this kind of "database" as simple as it could be and make it part of my code

I.e. some locations codes are: CA1405, CE8574, MN7891, RC1020, PR5471, LK6871 ... etc.

That's what I have.

        Allrows = Range("A" & Rows.Count).End(xlUp).row
 
    Dim fila As Integer
        fila = 2
    Do While (fila <= Allrows)
        rango1 = "D" & CStr(fila)
        Allrows = Range("A" & Rows.Count).End(xlUp).row
        If (Range(rango1) = [COLOR=seagreen]"Locations codes"[/COLOR]) Then
        Rows(fila).Select
        Selection.Delete Shift:=xlUp
        fila = fila - 1
        End If
        fila = fila + 1
    Loop

Thank you very much !!! any help will be very apreciate.

 

[tigeravatar]

Barbu,

I was unable to duplicate that error.
I created two standard modules, Module1 and Module2

In Module2, I put in the CodesToDelete code (and nothing else):

Public CodesToDelete As Variant
 
Sub Assign_CodesToDelete()
    CodesToDelete = Array("80186", "80205", "80320", "80321", "80340", "80343", "80344", "80363", "80366", "80539")
End Sub

In Module1, I put in the Sub tgr code (and nothing else. Also, I made a slight change within the Intersect line of code. It should be noted that the code ran successfully with and without the change on my test worksheet):

Sub tgr()
 
    Call Module2.Assign_CodesToDelete
 
    With Intersect(ActiveSheet.UsedRange.EntireRow, Range("A:D"))
        .AutoFilter 4, Module2.CodesToDelete, xlFilterValues
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
 
End Sub

Also note that this code is designed to run on the Active worksheet. If you are looking at a sheet other than the one you want to run the code on, you may get the error you mentioned.

 

[Barbu]

p45cal

I already tested your new code, and it works perfectly, I really appreciate all your help, It's great to know that someone it's willing to share his knowledge.
Also I would like to know something regarding to the InStr function you used (because I have never seen that before), how can I use it in the opposite way, telling to the IF function that delete those lines that are different from the CodesToLose list ??

Thanks !!!

 

[p45cal]

(untested) change:

If InStr(CodesToLose, Cells(i, "D")) [COLOR=Red]>[/COLOR] 0 And Len(Cells(i, "D").Value) > 0 Then Rows(i).Delete

to:

If InStr(CodesToLose, Cells(i, "D")) [COLOR=Red]=[/COLOR] 0 And Len(Cells(i, "D").Value) > 0 Then Rows(i).Delete

 

[Barbu]

Tigeravatar,

I already tried exactly as you describe it the last time, and it works !!!, I don't know why I was getting that error (probably I was doing something wrong). Thank you very much for all your help !!!

P45cal,

the sign change works perfectly, thank you so much !!!