VBA:比较两个范围的更快方法? [英] VBA: faster way to compare two ranges?

问题描述

我需要比较两个范围，看看一个范围内的值是否出现在另一个范围内.这是我使用的代码:

I need to compare two ranges and see if value in one range appears in the other. This is the code I use:

Dim rng1 As Range
Dim rng2 As Range
Dim cell as Range
Dim found as Range

set rng1 = ....
set rng2 = ....
for each cell in rng1
set found = rng2.Find(what:=cell,.....
Next cell

如果范围是数千行单列，则此代码是可以的.数以万计的时候，它非常慢.

This code is OK if the range is in thousands of rows, single column. When it comes to tens of thousands, it's very slow.

是否要加快速度?

推荐答案

这可能是处理大量数据的最快方法:

This might be the fastest way for large amounts of data:

Option Explicit
Sub Test()

    Dim rng1 As Range
    Set rng1 = YourShorterRange

    Dim rng2 As Range
    Set rng2 = YourLargerRange

    Dim C As Range
    Dim Matches As Object: Set Matches = CreateObject("Scripting.Dictionary")
    'input the larger data inside a dictionary
    For Each C In rng2
        If Not Matches.Exists(C.Value) Then Matches.Add C.Value, 1
    Next C

    Dim i As Long
    Dim arr As Variant
    'input the shorter data inside an array
    arr = rng1.Value
    For i = 1 To UBound(arr)
        If Matches.Exists(arr(i, 1)) Then
            'your code if the value is found
        End If
    Next i

End Sub

为多利安(Dorian)

Edit for Dorian:

Option Explicit
Sub Test()

    Dim rng1 As Range
    Set rng1 = YourShorterRange

    Dim rng2 As Range
    Set rng2 = YourLargerRange

    Dim i As Long, j As Long
    Dim arr As Variant
    Dim Matches As Object: Set Matches = CreateObject("Scripting.Dictionary")
    arr = rng1.Value
    'input the larger data inside a dictionary
    For i = 1 To UBound(arr)
        For j = 1 To UBound(arr, 2)
            If Not Matches.Exists(arr(i, j)) Then Matches.Add arr(i, j), 1
        Next j
    Next i

    'input the shorter data inside an array
    arr = rng2.Value
    For i = 1 To UBound(arr)
        For j = 1 To UBound(arr, 2)
            If Matches.Exists(arr(i, j)) Then
                'your code if the value is found
            End If
        Next j
    Next i

End Sub

这篇关于VBA:比较两个范围的更快方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！