Fastest Search Excel VBA Codes

The Basic Worksheet Setup

I created a worksheet and populated 5 columns and 1,048,576 rows with the text “aaa” in the range A1:E1048576. At the last cell in this range E1048576, I put in the value “bbb” which will be my search target. For the next few sections, I will attempt to write VBA codes to search through 5.2 million cells and measure the time taken for a VBA code to find this particular cell with the text value “bbb”.

Worse search method using FOR NEXT loops on a Worksheet. 14.6 seconds

I wrote the following VBA function Func_Search_Range_With_FOR_NEXT to search for a range in a worksheet for a certain target value.

Function Func_Search_Range_With_FOR_NEXT( _
ByRef rngSearch As Range, _
ByVal strTarget As String _
) As Variant

It uses a nested FOR NEXT loop to search through every cell within a range and tries to find a cell that matches the target value. On my laptop, I measured an average of 14.6 seconds to find the target cell E1048576 in a range with 5.2 million cells.

This was the slowest search VBA code because it does not use the faster native Excel search functions like MATCH or FIND to perform this massive search. It simply instructs the entire Excel application to look at each cell within a range. The VBA code for this function is listed below:

'*************************************************************
'Purpose: Search a range and return an array showing the row
'and column location of the target value.
'rngSearch - specify the range to search
'strTarget - specify the value to search for
'*************************************************************
Function Func_Search_Range_With_FOR_NEXT( _
ByRef rngSearch As Range, _
ByVal strTarget As String _
) As Variant
    'declare variables
    Dim blnFound As Boolean 'declare a flag to indicate target is found
    Dim lgRow_Current As Long 'declare a row counter
    Dim lgCol_Current As Long 'declare a column counter
    Dim arrOutput As Variant 'declare output array (row found, col found)
    
    'set output array to not found as default
    ReDim arrOutput(1 To 1, 1 To 2) 'create output array
    arrOutput(1, 1) = -1 'set default to -1
    arrOutput(1, 2) = -1 'set default to -1
    
    'search columns using FOR NEXT
    For lgCol_Current = 1 To rngSearch.Columns.Count
        'search rows using FOR NEXT
        For lgRow_Current = 1 To rngSearch.Rows.Count
            If rngSearch.Cells(lgRow_Current, lgCol_Current) = strTarget Then
                'determine the results
                arrOutput(1, 1) = lgRow_Current 'found row location
                arrOutput(1, 2) = lgCol_Current 'found column location
                blnFound = True
                Exit For 'exit for loop
            End If
        Next lgRow_Current
        If blnFound = True Then Exit For 'exit for loop
    Next lgCol_Current
    
    'output the result
    Func_Search_Range_With_FOR_NEXT = arrOutput
End Function

The above function was driven by this subroutine.

'*************************************************************
'Purpose: Measure the time elapsed to execute:
'Func_Search_Range_With_FIND
'*************************************************************
Sub Search_Using_FIND()
    Dim arrFound As Variant
    
    Call MilliSecond_Timer_Start 'start timer
    arrFound = Func_Search_Range_With_FIND(ActiveSheet.Range("A1:E1048576"), "bbb")
    Call MilliSecond_Timer_End 'start timer

    Debug.Print "Found Row: " & arrFound(1, 1) & vbNewLine & "Found Col:" & arrFound(1, 2)
End Sub

Using FOR NEXT loops on an array. 5.1 seconds

I believe I can improve the search speed by putting all the cells in the search range in an array and perform the range search using VBA in the same way. So instead of having Excel access cells in a worksheet which is slower, it will perform the search within arrays residing in the computer’s faster RAM memory. The code was changed slightly below. It will transfer all the 5.2 million cell contents into an array and the search will be performed in the exact manner as before.

The average search time was reduced to 5.1 seconds. A 65% speed improvement.

'*************************************************************
'Purpose: Search a range and return an array showing the row
'and column location of the target value.
'rngSearch - specify the range to search
'strTarget - specify the value to search for
'*************************************************************
Function Func_Search_Range_With_ARRAYS( _
ByRef rngSearch As Range, _
ByVal strTarget As String _
) As Variant
    'declare variables
    Dim vntSearchArray As Variant 'declare an array
    Dim blnFound As Boolean 'declare a flag to indicate target is found
    Dim lgRow_Current As Long 'declare a row counter
    Dim lgCol_Current As Long 'declare a column counter
    Dim arrOutput As Variant 'declare output array (row found, col found)
    
    'set output array to not found as default
    ReDim arrOutput(1 To 1, 1 To 2) 'create output array
    arrOutput(1, 1) = -1 'set default to -1
    arrOutput(1, 2) = -1 'set default to -1
        
    'copy the range into an array
    vntSearchArray = rngSearch
    
    'determine array size
    Dim lgMinRow As Long, lgMaxRow As Long, lgMinCol As Long, lgMaxCol As Long
    lgMinRow = LBound(vntSearchArray, 1)
    lgMaxRow = UBound(vntSearchArray, 1)
    lgMinCol = LBound(vntSearchArray, 2)
    lgMaxCol = UBound(vntSearchArray, 2)
    
    'scan the array
    For lgRow_Current = lgMinRow To lgMaxRow 'scan rows
        For lgCol_Current = lgMinCol To lgMaxCol 'scan cols
            If vntSearchArray(lgRow_Current, lgCol_Current) = strTarget Then 'a match is found
                'determine the results
                arrOutput(1, 1) = lgRow_Current 'found row location
                arrOutput(1, 2) = lgCol_Current 'found column location
                blnFound = True
                Exit For 'exit if found in current col
            End If
        Next lgCol_Current
        If blnFound = True Then
            Exit For 'exit if found in current col
        End If
    Next lgRow_Current
    
    'output the result
    Func_Search_Range_With_ARRAYS = arrOutput
End Function

FIND Excel VBA search method. It’s faster & flexible! 4.2 seconds

I experimented on the same range by using Excel’s native FIND function. This is an Excel search feature when you hit “CTRL-F” on a worksheet. I coded the equivalent using VBA with the following code. Compared to using nested FOR NEXT loops, the Excel’s FIND is a lot faster and offers flexible search options like:

• Searching by row or column order.
• Match by the entire cell contents or part of the cell contents.
• Search cell comments, cell values or cell formulas.

You can refer to Microsoft’s excellent documentation on the Range.FIND method.

The average search time using FIND was reduced further to 4.2 seconds. A 71% speed improvement.

'****************************************************
'Purpose: Search a range and return an array showing the row
'and column location of the target value.
'rngSearch - specify the range to search
'strTarget - specify the value to search for
'****************************************************
Function Func_Search_Range_With_FIND( _
ByRef rngSearch As Range, _
ByVal strTarget As String _
) As Variant
    'declare variables
    Dim rngFoundCell As Range 'declare found cell object
    Dim rngLastCell As Range 'declare the last cell in search range
    Dim arrOutput As Variant 'declare output array (row found, col found)
    
    'set output array to not found as default
    ReDim arrOutput(1 To 1, 1 To 2) 'create output array
    arrOutput(1, 1) = -1 'set default to -1
    arrOutput(1, 2) = -1 'set default to -1
    
    'set last cell within the search range
    Set rngLastCell = rngSearch.Cells(rngSearch.Count)
    
    'search for the target start after the last cell using FIND
    'options:
    'SearchOrder:=xlByColumns
    'LookAt:=xlPart
    Set rngFoundCell = rngSearch.Find(What:=strTarget, After:=rngLastCell, SearchOrder:=xlByRows, LookAt:=xlWhole, LookIn:=xlValues)
    
    'determine the results
    If Not (rngFoundCell Is Nothing) Then
        arrOutput(1, 1) = rngFoundCell.Row 'found row location
        arrOutput(1, 2) = rngFoundCell.Column 'found column location
    End If
    
    'output the result
    Func_Search_Range_With_FIND = arrOutput
End Function

MATCH VBA search method. Fastest! 0.15 seconds!

MATCH is an Excel function that returns the relative position of an item in an array that matches a specified value in a specified order. MATCH only works on 1-dimensional arrays. So our code needs to “split” a range into multiple “strips” of cells we perform the search on each “strip”.

Th code is more complicated but the speed performance is super fast. I can easily get the result in 0.15 seconds, almost 100% improvement.

'****************************************************
'Purpose: Search a range and return an array showing the row
'and column location of the target value.
'rngSearch - specify the range to search
'strTarget - specify the value to search for
'****************************************************
Function Func_Search_Range_With_MATCH( _
ByRef rngSearch As Range, _
ByVal strTarget As String _
) As Variant
    'declare variables
    Dim arrOutput As Variant 'specify output array (row found, col found)
    
    'set output array to not found as default
    ReDim arrOutput(1 To 1, 1 To 2) 'create output array
    arrOutput(1, 1) = -1 'set default to -1
    arrOutput(1, 2) = -1 'set default to -1
        
    'determine size of search range
    Dim lgRowSize As Long
    Dim lgColSize As Long
    lgRowSize = rngSearch.Rows.Count
    lgColSize = rngSearch.Columns.Count
    
    'break the search range into a "strip" of continguous cells by row or column depending which is smaller or optimise speed
    'search each strip using MATCH
    Dim rngStrip As Range 'declare a "strip" of cells
    Dim lgStripCounter As Long 'declare a "strip" counter
    Dim vntRelativeLocation As Variant 'specify the relative location of found target within the "strip"
    Dim lgAbsoluteStartLocation As Long 'absolute start column no: or row no: of a "strip"
    On Error Resume Next
    If lgColSize < lgRowSize Then 'smaller no: of columns
        'search by columns
        For lgStripCounter = 1 To lgColSize
            lgAbsoluteStartLocation = rngSearch.Columns(lgStripCounter).Cells(1, 1).Column 'determine the start location of the first cell in strip
            vntRelativeLocation = Application.WorksheetFunction.Match(strTarget, rngSearch.Columns(lgStripCounter), 0) 'search the target using MATCH in the strip
            If Err.Number = 0 Then
                'determine the results
                arrOutput(1, 1) = vntRelativeLocation + lgAbsoluteStartLocation - 1 'found row location
                arrOutput(1, 2) = lgStripCounter 'found column location
                Exit For
            Else
                Err.Clear
            End If
        Next
    Else 'smaller no: of rows or equal rows and columns
        For lgStripCounter = 1 To lgRowSize
            lgAbsoluteStartLocation = rngSearch.Rows(lgStripCounter).Cells(1, 1).Row 'determine the start location of the first cell in strip
            vntRelativeLocation = Application.WorksheetFunction.Match(strTarget, rngSearch.Rows(lgStripCounter), 0) 'search the target using MATCH in the strip
            If Err.Number = 0 Then
                'determine the results
                arrOutput(1, 1) = vntRelativeLocation + lgAbsoluteStartLocation - 1 'found row location
                arrOutput(1, 2) = vntRelativeLocation 'found column location
                Exit For
            Else
                Err.Clear
            End If
        Next
    End If
    
    'output the result
    Func_Search_Range_With_MATCH = arrOutput
End Function

Conclusion

Feel free to reuse the code here. If you have any better ideas to improve upon what I have done, do share!

Aeternus Consulting is the premier training centre in Singapore for Excel Courses – Basic Excel, Advanced Excel and Excel VBA Macro courses. For more Microsoft Excel training courses, please visit our Excel training page.

Aeternus Consulting is now on Instagram! So take a look and follow us on Instagram.