从 Excel 将唯一值填充到 VBA 数组中 [英] Populate unique values into a VBA array from Excel

问题描述

谁能给我 VBA 代码，从 Excel 工作表中获取一个范围(行或列)并用唯一值填充列表/数组，即:

Can anyone give me VBA code that will take a range (row or column) from an Excel sheet and populate a list/array with the unique values, i.e.:

table
table
chair
table
stool
stool
stool
chair

当宏运行时会创建一个数组，例如:

when the macro runs would create an array some thing like:

fur[0]=table
fur[1]=chair
fur[2]=stool

推荐答案

在这种情况下，我总是使用这样的代码(只要确保您选择的分隔符不是搜索范围的一部分)

In this situation I always use code like this (just make sure delimeter you've chosen is not a part of search range)

Dim tmp As String
Dim arr() As String

If Not Selection Is Nothing Then
   For Each cell In Selection
      If (cell <> "") And (InStr(tmp, cell) = 0) Then
        tmp = tmp & cell & "|"
      End If
   Next cell
End If

If Len(tmp) > 0 Then tmp = Left(tmp, Len(tmp) - 1)

arr = Split(tmp, "|")

这篇关于从 Excel 将唯一值填充到 VBA 数组中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！