| |
>転記するデータをいったん配列に入れておき、
>最後に一括して シートに転記するようにすれば、
>もっと速く処理できますけど。
Sub test3_Dictionary2()
Const MAX = 20000
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim r1 As Range, r2 As Range
Dim LstR1 As Long, LstR2 As Long
Dim i&, v, u, k&
Dim dic As Object
Dim t&
t = timeGetTime()
Set dic = CreateObject("Scripting.Dictionary")
Set WS1 = Worksheets(1)
Set WS2 = Worksheets(2)
With WS1
LstR1 = MAX '.Cells(Rows.Count, 1).End(xlUp).Row
v = .Range("A1:A" & LstR1).Value
For i = 1 To UBound(v)
dic(v(i, 1)) = Empty
Next
ReDim u(1 To UBound(v), 1 To 1)
LstR2 = MAX ' WS2.Cells(Rows.Count, 1).End(xlUp).Row
'WS2 A列をLoop
v = WS2.Range("A1:A" & LstR2).Value
For i = 1 To UBound(v)
'Bk2シートA列のあるセル値が、Bk1の辞書にあるか調べる
If Not dic.Exists(v(i, 1)) Then
'↓無かった時の処理
k = k + 1
u(k, 1) = v(i, 1)
End If
Next i
If k Then
.Cells(LstR1 + 1, 1).Resize(k).Value = u
End If
End With
Debug.Print "Dict_arry", timeGetTime() - t
End Sub
■上と同じ ２万行データの 結果です
Dict_arry 85
|
|
kanabun
- 12/2/3(金) 19:08 -
