📎 This article includes 1 downloadable practice file ↓
Looping through 50,000 rows and calling VLOOKUP for each one is slow. A Dictionary stores key → value pairs in memory and finds any key instantly.
Create one
Dim d As Object
Set d = CreateObject("Scripting.Dictionary") ' late binding: no reference needed
d.CompareMode = vbTextCompare ' "delhi" = "Delhi"Unique list with counts
Sub CountByRegion()
Dim d As Object, arr, i As Long, k
Set d = CreateObject("Scripting.Dictionary")
arr = Sheets("Data").Range("B2:B50001").Value ' read once into memory
For i = 1 To UBound(arr)
If arr(i, 1) <> "" Then d(arr(i, 1)) = d(arr(i, 1)) + 1
Next i
i = 2
For Each k In d.Keys
Sheets("Summary").Cells(i, 1).Value = k
Sheets("Summary").Cells(i, 2).Value = d(k)
i = i + 1
Next k
End SubReplace a lookup loop
' load price list: code -> price
For i = 2 To lastPrice: prices(wsP.Cells(i, 1).Value) = wsP.Cells(i, 3).Value: Next i
' then, for each order row:
If prices.Exists(code) Then price = prices(code) Else price = "Not found"Useful members
| Member | Does |
|---|---|
.Exists(key) |
Is the key there? |
.Count |
Number of keys |
.Keys / .Items |
Arrays of keys / values |
.Remove key, .RemoveAll |
Delete entries |
💡 Reading the range into an array first (
arr = Range(...).Value) matters as much as the dictionary. Together they often turn minutes into under a second.📎 Practice files for this article
- 📗Dictionary practice workbook (.xlsm)5,000 orders with messy region names. Two macros: one merges variants correctly, one shows what goes wrong without cleaning.⬇ XLSM · 145 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong