asked on

Excel VBA: Find values & Put in array

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.

Example:
A. B
144. ddgh
24477. 355:+fd
Hello World aaaa
1188. &554&
ffhj
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.

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ouestque

ASKER

Really cool! Awesome code! Thanks!

Martin Liss

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
        Next
    Next
        .AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

Open in new window

Excel VBA: Find values &amp; Put in array

Excel VBA: Find values & Put in array