更快地获取VBA中列的所有唯一值？ [英] Quicker way to get all unique values of a column in VBA?

问题描述

 设置数据= ws.UsedRange 
 
设置唯一= CreateObject（Scripting.Dictionary）
 
错误恢复下一步
对于x = 1对data.Rows.Count 
 unique.Add数据（x，some_column_number） .Value，1 
下一个x 
错误GoTo 0 
  

此时 unique.keys 得到我需要的东西，但循环本身似乎对于具有成千上万条记录的文件来说非常慢（而这根本不是一个问题在一个类似Python或C ++的语言中）。

解决方案

将数值加载到数组中会更快：

  Dim data（），unique As Object，r As Long 
 data = ws.UsedRange.Value 
 
设置unique = CreateObject（Scripting.Dictionary）
对于r = 1到UBound（数据）
 unique（data（r，some_column_number））= Empty 
下一个r 
  

你也应该考虑耳朵对于Scripting.Dictionary的绑定：

  Dim unique As New Scripting.Dictionary 
  

Is there a faster way to do this?

Set data = ws.UsedRange

Set unique = CreateObject("Scripting.Dictionary")

On Error Resume Next
For x = 1 To data.Rows.Count
    unique.Add data(x, some_column_number).Value, 1
Next x
On Error GoTo 0

At this point unique.keys gets what I need, but the loop itself seems to be very slow for files that have tens of thousands of records (whereas this wouldn't be a problem at all in a language like Python or C++ especially).

解决方案

Loading the values in an array would be much faster:

Dim data(), unique As Object, r As Long
data = ws.UsedRange.Value

Set unique = CreateObject("Scripting.Dictionary")
For r = 1 To UBound(data)
    unique(data(r, some_column_number)) = Empty
Next r

You should also consider early binding for the Scripting.Dictionary:

Dim unique As New Scripting.Dictionary

这篇关于更快地获取VBA中列的所有唯一值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！