使用 VBA 从数组中删除重复项 [英] Remove duplicates from array using VBA

问题描述

假设我在 Excel 2010 中有一个数据块，100 行 x 3 列.

Assume I have a block of data in Excel 2010, 100 rows by 3 columns.

C 列包含一些重复项，比如说它开始于

Column C contains some duplicates, say it starts off as

1, 1, 1, 2, 3, 4, 5, ..... , 97, 98

1, 1, 1, 2, 3, 4, 5, ..... , 97, 98

使用 VBA，我想删除重复的行，所以我只剩下 98 行和 3 列.

Using VBA, I would like to remove the duplicate rows so I am left with 98 rows and 3 columns.

1, 2, 3, ..... , 97, 98

1, 2, 3, ..... , 97, 98

我知道 Excel 2010 中有一个按钮可以执行此操作，但它随后会干扰我的其余代码并给出不正确的结果.

I know there is a button in Excel 2010 to do that but it inteferes with the rest of my code subsequently and gives incorrect results.

此外，我想在数组中进行，然后将结果粘贴到工作表上，而不是诸如Application.Worksheetfunction.countif(.....

Furthermore, I would like to do it in arrays, then paste the results on the worksheet, rather than methods such as Application.Worksheetfunction.countif(.....

比如:

Dim myarray() as Variant
myarray=cells(1,1).Currentregion.value

Dim a as Long

For a=1 to Ubound(myarray,1)

    'something here to 

Next a

推荐答案

Function eliminateDuplicate(poArr As Variant) As Variant
    Dim poArrNoDup()

    dupArrIndex = -1
    For i = LBound(poArr) To UBound(poArr)
        dupBool = False

        For j = LBound(poArr) To i
            If poArr(i) = poArr(j) And Not i = j Then
                dupBool = True
            End If
        Next j

        If dupBool = False Then
            dupArrIndex = dupArrIndex + 1
            ReDim Preserve poArrNoDup(dupArrIndex)
            poArrNoDup(dupArrIndex) = poArr(i)
        End If
    Next i

    eliminateDuplicate = poArrNoDup
End Function

这篇关于使用 VBA 从数组中删除重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！