Home / Questions / 14530
Accepted
Priscila Estala
Asked: 2020-06-21 13:17:35 +0800 CST 2020-06-21 13:17:35 +0800 CST

If string value in array then...vba

  • 772

I'm working with a macro and what I want to do is compare the values ​​of a column against the values ​​in an array and if it finds it delete the entire row, what I don't know is the instruction to find the value, my code:

Dim i, c, last_row As Integer
Dim myarray As Variant
Dim Product As String

myarray = Array("J8031A#ACC", "J8031A#ACQ", "A2W80A")
last_row = wsMissingSCM2.Range("A22").End(xlDown).Row

For c = 22 To last_row

 Product = wsMissingSCM2.Cells(c, 4).Value

 **If IsArray(Product) Then** 'Tengo este codigo pero no me funciona

    Rows(c).EntireRow.Delete

 End If
Next c
array

1 Answers

  1. Best Answer

    From this answer on SO , what you need is a helper function that checks if a value exists in an array:

    Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

    And now yes, use the function to do what you need when you find the value:

    ' ...
For c = 22 to last_row
    Product = wsMissingSCM2.Cells(c,4).value
    If IsInArray(Product, myarray) then
        Rows(c).EntireRow.Delete
    End If
next c
' ...
    • 3