Excel VBA: Find values & Put in array

ouestque used Ask the Experts™
Column A & B can contain over 500,000 values and changes over time.

What is the fastest code that will find all cells in column A with "Hello World" and put the corresponding value(s) from column B in an array without iterating through all the rows.

A.                      B
144.                  ddgh
24477.             355:+fd
Hello World     aaaa
1188.               &554&
Hello.               xxccffee
Hello World     bbbb

In the above example it would put aaaa and bbbb in an array because "Hello World" is in column A for them both.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Analyst Assistant
I don't think there is a way to do it without looping, though you could speed things up considerably by putting the data into an array at the start.
Dim arrIn As Variant
Dim arrOut()
Dim cnt As Long
Dim I As Long

    arrIn = Range("A1").CurrentRegion

    ReDim arrOut(1 To UBound(arrIn))

    For I = LBound(arrIn) To UBound(arrIn)
        If arrIn(I, 1) = "Hello World" Then
            cnt = cnt + 1
            arrOut(cnt) = arrIn(I, 2)
        End If
    Next I

    Range("E1").Resize(UBound(arrOut)).Value = Application.Transpose(arrOut)

Open in new window


Really cool! Awesome code! Thanks!
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

This may be faster. It assumes that there is a one-row header,
Sub MakeArray()

Dim strColumnB() As String
Dim lngArea As Integer
Dim rngVisible As Range
Dim lngRow As Long
Dim lngCount As Long

Application.ScreenUpdating = False

' Column 1 is the column that contains "Hello World".
With ActiveSheet.UsedRange
    .AutoFilter Field:=1, Criteria1:="Hello World"
    Set rngVisible = ActiveSheet.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible)

    ReDim strColumnB(.SpecialCells(xlCellTypeVisible).Rows.Count + 1)
    For lngArea = 1 To rngVisible.Areas.Count
        For lngRow = 1 To rngVisible.Areas(lngArea).Rows.Count
            strColumnB(lngCount) = rngVisible.Areas(lngArea).Rows(lngRow).Cells(lngRow, "B")
            Debug.Print strColumnB(lngCount)
            lngCount = lngCount + 1
    End With
    Application.ScreenUpdating = True
End Sub

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial