Handling Large Data in VBA: Arrays, Dictionary & AutoFilter

Work with hundreds of thousands of rows efficiently.

---

Arrays for speed

Read once, compute in memory, write once.

Dim data, r As Long
data = Range("A2:C500000").Value
For r = 1 To UBound(data, 1)
    If data[r, 3] < 0 Then data[r, 3] = 0
Next r
Range("A2").Resize(UBound(data, 1), 3).Value = data

---

Dictionary for lookups / dedup

Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = vbTextCompare
dict("ALPHA") = 1
If Not dict.Exists("BETA") Then dict.Add "BETA", 2

---

AutoFilter to reduce iterations

Filter, then copy visible cells only:

With Range("A1").CurrentRegion
    .AutoFilter Field:=2, Criteria1:=">1000"
    .SpecialCells(xlCellTypeVisible).Copy Destination:=Worksheets("Out").Range("A1")
End With