Getting Rid of .Find in VBA Code I have a range of two columns that I want to search for a &quot;key&quot; and then find the corresponding value in the directly adjacent column. The key is stored in a dictionary that I populated earlier in the program. I then store the value with its corresponding key in the dictionary to display later.
I am using .Find to search the range for the key and then getting the value using .Offset to get the adjacent cell. The code follows:
Dim key As Variant
Dim filRange As Range
Dim found As Range

Set filRange = ws.Range(&quot;D2:E36419&quot;).SpecialCells(xlCellTypeVisible)
 
Dim count As Integer
Dim i As Long
 
For i = 0 To partsDict.count - 1
    key = partsDict.Keys(i)
    Set found = filRange.Find(key)

    If Not found Is Nothing Then
        count = found.Offset(0, 1).value
        partsDict(i) = count
    Else
        partsDict(i) = Empty
    End If

Next i

The code functions as expected and I am able to print all values later in the program. The issue is the program takes over 15 seconds to run. I've seen the .Find method is slow and feel like there is a better way to search and retrieve my values. Should I store the range in an array somehow? Use another dictionary? Thanks!
Set filRange = ws.Range(&quot;D2:E36419&quot;).SpecialCells(xlCellTypeVisible)


Hard coding range references will makes your code unnecessarily inflexible.  It is best to create a dynamic range reference that will resize itself to fit the data.

With ws
    Set filRange = .Range(&quot;D2:E&quot; &amp; .Rows.Count).End(xlUp)
End With


The filRange  is set to 2 columns.  I am assuming that column 1 is the key column and column 2 is the value column.  If this is the case then you should either adjust your fill range:

With ws
    Set filRange = .Range(&quot;D2&quot; &amp; Cells(.Rows.Count, &quot;D&quot;).End(xlUp))
End With


Or adjust your search:

Set found = filRange.Columns(1).Find(key)


Range.CurrentRegion is a convenient way to create a dynamic range.

Set filRange = ws.CurrentRegion.Columns(&quot;D&quot;)

Question:

Do we think putting every pair in a new Dictionary structure would work? I'm thinking once everything is in the Dictionary, searching for my keys should be quick (does Dictionary use hashing?).

Answer:
Yes and yes.  Dictionaries use hashing for super fast look ups.  You may find this article interesting EXCEL VLOOKUP VS INDEX MATCH VS SQL VS VBA.
The reason that we use dictionaries in the first place is for the super fast look ups.  The problem in your project setup is that you are using Range.Find() for your lookups.
Its hard to give advice about what is the best approach with just a small snippet of code. Proving a more detailed question with all your relevant code, data samples, and perhaps a test stub will give you the best results.
Solution
Whatever you decide to do the key is to loop over the range values once and use the dictionary lookup up the values.  Personally, I would write a function that returns a dictionary that holds the filtered keys and values and compare it to partsDict.
Function GetFilteredRangeMap(Target As Range, KeyColumn As Variant, ValueColumnOffset As Variant) As Scripting.Dictionary
    
    Dim Column As Range
    
    Rem Set Column to the Visible Cells in the Key Column Range
    With Target.CurrentRegion.Columns(KeyColumn)
        On Error Resume Next
        Set Column = .SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
    End With
    
    If Not Column Is Nothing Then
        Dim Map As New Scripting.Dictionary
        Dim Cell As Range
        For Each Cell In Column
            Map(KeyPrefix &amp; Cell.Value) = Cell.Offset(ValueColumnOffset)
        Next
    End If
    
    Set GetFilteredRangeMap = Map
End Function

Usage

Dim Target As Range
Set Target = Sheet2.Range(&quot;A1&quot;).CurrentRegion

Dim Map As New Scripting.Dictionary
Set Map = GetFilteredRangeMap(Target, 1, 2)