VBA，从数组中删除重复 [英] VBA, remove duplicates from array

问题描述

有人可以给我一些关于如何解决以下问题的指针：

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

列C包含一些重复项，说起始为1,1,1 2,3,4,5，...，97,98

使用VBA，我想根据C列删除duplcate行，以便我留下1,2,3，.....，97 ，98，即只有98行和3列。

我知道有一个按钮，我可以点击Excel 2010做这个，但我想在VBA （因为我已经尝试了这个，因为某种原因，随后与其余的代码混淆，并给出了不正确的结果）。

此外，我想在数组中，然后将结果粘贴在工作表上，而不是像Application.Worksheetfunction.countif（.....

这样的方法，如：

  Dim myarray（）as Variant 
 myarray = cells（1,1）.Currentregion.value 
 
 Dim a as Long 
 
对于a = 1到Ubound（myarray，1）
 
'这里的东西
 
下一个
  

解决方案

我回答了类似的问题。这是我使用的代码：

  Dim dict As Object 
 Dim rowCount As Long 
 Dim strVal As String 
 
设置dict = CreateObject（Scripting.Dictionary）
 
 rowCount = Sheet1.Range（A1）。CurrentRegion.Rows.Count 
 
'您可以更改循环条件以遍历数组行而不是
 Do While rowCount> 1 
 strVal = Sheet1.Cells（rowCount，1）.Value2 
 
如果dict.exists（strVal）然后
 Sheet1.Rows（rowCount）.EntireRow.Delete 
 Else 
'如果使用数组执行此操作，然后在Else块
'中添加代码，将此行中的值分配给唯一值的数组
 dict.Add strVal，0 
结束If 
 
 rowCount = rowCount  -  1 
循环
 
设置dict = Nothing 
  

如果要使用数组，则循环使用相同条件（if / else）语句的元素。如果该词典中不存在该项目，则可以将其添加到字典中，并将行值添加到另一个数组。

老实说，我认为最有效率方法是调整从宏记录器获得的代码。您可以在一行中执行上述功能：

  Sheet1.UsedRange.RemoveDuplicates列：= 3，标题：= xlYes 
  

Can someone please give me some pointers as to how to solve the following problem:

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

Column C contains some duplicates, say it starts off as 1,1,1 2,3,4,5, ..... ,97,98

Using VBA, I would like to remove the duplcate rows, according by column C, so that I am left with 1,2,3, ..... , 97, 98, ie only 98 rows and 3 columns.

I know there is a button I can click in Excel 2010 to do just that but I want to do it in VBA (because I have tried this and for some reason, inteferes with the rest of my code subsequently and gives incorrect results).

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

So something like:

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 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屋！