Store matching values in an array with VBA

[Retroshift]

Hello,
Column A is filled with string values. Column B is sometimes filled with the value "x".
I would like to write VBA code that returns all the string values from column A into an array if they match the "x" value from column B.
Anyone knows how to do this?
Thanks

 

[Domenic]

Try...

Sub test()

    Dim rng As Range
    Set rng = Range("A2:B" & Cells(Rows.Count, "A").End(xlUp).Row)
 
    Dim arr As Variant
    With Application
        arr = .Filter(rng.Columns(1), .Evaluate(rng.Columns(2).Address & "=""x"""))
    End With
 
    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        Debug.Print arr(i, 1)
    Next i

End Sub

Alternatively, we can avoid the use of the Evaluate method and use XLookup instead...

Sub test2()

    Dim rng As Range
    Set rng = Range("A2:B" & Cells(Rows.Count, "A").End(xlUp).Row)
 
    Dim arr As Variant
    With Application
          arr = .Filter(rng.Columns(1), .XLookup(rng.Columns(2), "x", True, False))
    End With
 
    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        Debug.Print arr(i, 1)
    Next i

End Sub

Hope this helps!

 

[Retroshift]

Hi Domenic. Unfortunately, the filter function is not available in my version of Excel (Office 2019). Are there any alternatives to your code?

 

[Domenic]

In that case, try the following instead...

Sub test()

    Dim rng As Range
    Set rng = Range("A2:B" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    Dim cnt As Long
    cnt = Application.CountIf(rng.Columns(2), "X")
    
    ReDim resultArray(1 To cnt)

    Dim i As Long
    Dim j As Long
    j = 1
    With rng
        For i = 1 To .Rows.Count
            If UCase(.Cells(i, 2).Value) = "X" Then
                resultArray(j) = .Cells(i, 1).Value
                j = j + 1
            End If
        Next i
    End With
    
    For i = LBound(resultArray) To UBound(resultArray)
        Debug.Print resultArray(i)
    Next i
    
End Sub

Hope this helps!

 

[Retroshift]

Thanks Domenic

 

[Domenic]

You're very welcome, cheers!