使用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屋!