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

问题描述

假设我在Excel 2010中有一个数据块，100行乘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

推荐答案

我回答了类似的问题.这是我使用的代码:

I answered a similar question. Here is the code I used:

Dim dict As Object
Dim rowCount As Long
Dim strVal As String

Set dict = CreateObject("Scripting.Dictionary")

rowCount = Sheet1.Range("A1").CurrentRegion.Rows.Count

'you can change the loop condition to iterate through the array rows instead
Do While rowCount > 1
  strVal = Sheet1.Cells(rowCount, 1).Value2

  If dict.exists(strVal) Then
    Sheet1.Rows(rowCount).EntireRow.Delete
  Else
    'if doing this with an array, then add code in the Else block
    ' to assign values from this row to the array of unique values
    dict.Add strVal, 0
  End If

  rowCount = rowCount - 1
Loop

Set dict = Nothing

如果要使用数组，请使用相同的条件(if/else)语句遍历元素.如果该项目在字典中不存在，则可以将其添加到字典中，并将行值添加到另一个数组中.

If you want to use an array, then loop through the elements with the same conditional (if/else) statements. If the item doesn't exist in the dictionary, then you can add it to the dictionary and add the row values to another array.

老实说，我认为最有效的方法是调整从宏记录器获取的代码.您可以一行执行上述功能:

Honestly, I think the most efficient way is to adapt code you'd get from the macro recorder. You can perform the above function in one line:

    Sheet1.UsedRange.RemoveDuplicates Columns:=3, Header:=xlYes

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