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.
6 Responses
Fantastic! Congratulations.
I tested it but your result is not 100%.
Func_Search_Range_With_MATCH
Found Row: 1048580
Found Col:5
It supposed to be 1048576.
Another issue found is when there are more than 1 value found. It just points the first occurrence. Maybe some adjustment to continue search the whole Range.
Hello Juliano
Thanks to your feedback, I found the bug and fixed it.
Your suggestion to continue search the whole range is very challenging!
Is it faster just working with an arrange instead of an array?
thank you for help me
how are useing func?
Hello,
the match did not work with numbers
I replaced “bbb” with a number. Any ideas?
Thanks