我正在寻找一个宏,可以从A列中删除所有重复项。
输入:
我正在处理一大批数据,但 Excel 不太配合。似乎在网上找不到可行的解决方案。
谢谢!
输入:
John
Jimmy
Brenda
Brenda
Tom
Tom
Todd
输出:
John
Jimmy
Todd
我正在处理一大批数据,但 Excel 不太配合。似乎在网上找不到可行的解决方案。
谢谢!
John
Jimmy
Brenda
Brenda
Tom
Tom
Todd
输出:
John
Jimmy
Todd
然后将结果复制粘贴到新工作表中。
您可以在这里看到完整的过程。
否则,编写一个冗长的宏来检查值是否存在于集合中会很烦琐(需要递归循环)。虽然可以完成,但您真的需要吗?
但是,如果您想要实际删除所有相同的条目,则可以使用@Eoins的宏来完成任务,但需要进行一些修改,如下所示:
Option Explicit
Sub DeleteDuplicate()
Dim x, Y As Long
Dim LastRow As Long
Dim myCell As String
LastRow = Range("A1").SpecialCells(xlLastCell).Row
For x = LastRow To 1 Step -1
myCell = Range("A" & x).Text
If Application.WorksheetFunction.CountIf(Range("A1:A" & x), myCell) > 1 Then
For Y = x To 1 Step -1
If Range("A" & Y).Text = myCell Then
Range("A" & Y).EntireRow.Delete
End If
Next Y
End If
Next x
End Sub
1
。 - Ron Rosenfeld因为您需要宏,所以请尝试以下操作:
Excel 2007+
ActiveSheet.Range("A:A").RemoveDuplicates
这是您在Excel 2003中的选项:
Option Explicit
Sub DeletDuplicate()
Dim x As Long
Dim LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
Range("A" & x).EntireRow.Delete
End If
Next x
End Sub
如果您需要的话,这里有一个递归循环 :)
实际上,这是两个过程,第一个过程对列表进行排序,第二个过程则移除重复项
'----------------------------------------------------------------------
'--SORT A 1D ARRAY NUMERICALLY-ALPHABETICALLY(TAKEN FROM StackOverflow)
'----------------------------------------------------------------------
Public Sub QuickSort(vArray As Variant, inLow As Long, inHi As Long)
Dim pivot As Variant
Dim tmpSwap As Variant
Dim tmpLow As Long
Dim tmpHi As Long
tmpLow = inLow
tmpHi = inHi
pivot = vArray((inLow + inHi) \ 2)
While (tmpLow <= tmpHi)
While (vArray(tmpLow) < pivot And tmpLow < inHi)
tmpLow = tmpLow + 1
Wend
While (pivot < vArray(tmpHi) And tmpHi > inLow)
tmpHi = tmpHi - 1
Wend
If (tmpLow <= tmpHi) Then
tmpSwap = vArray(tmpLow)
vArray(tmpLow) = vArray(tmpHi)
vArray(tmpHi) = tmpSwap
tmpLow = tmpLow + 1
tmpHi = tmpHi - 1
End If
Wend
If (inLow < tmpHi) Then QuickSort vArray, inLow, tmpHi
If (tmpLow < inHi) Then QuickSort vArray, tmpLow, inHi
End Sub
'---------------------------------------
'--REMOVE DUPLICATES AND BLANKS FROM SORTED 1D ARRAY
'---------------------------------------
Public Function RemoveDuplicatesBlanks_1DSorted(Arr As Variant) As Variant
Dim i As Long, iMin As Long, iMax As Long, Cnt As Long
Dim TArr As Variant, TArr2() As Variant
TArr = Arr
iMin = LBound(TArr)
iMax = UBound(TArr)
i = iMin
Do While i <= iMax
If TArr(i) = vbNullString Then
Cnt = Cnt + 1
ElseIf i < iMax Then
If TArr(i) = TArr(i + 1) Then
TArr(i) = Empty
Cnt = Cnt + 1
End If
End If
i = i + 1
Loop
ReDim TArr2(iMin To (iMax - Cnt))
Cnt = iMin
For i = iMin To iMax
If Not TArr(i) = vbNullString Then
TArr2(Cnt) = TArr(i)
Cnt = Cnt + 1
End If
Next i
RemoveDuplicatesBlanks_1DSorted = TArr2
End Function
QuickSort MyArray, LBound(MyArray), UBOUND(MyArray)
MyArray = RemoveDuplicatesBlanks_1DSorted(MyArray)
这些方法仅适用于一维数组,如果您需要二维数组的话我也有相应的方法。
我已经多次使用这些方法,它们非常快速,比大多数方法都要快,所以如果您有大型列表,值得使用这些方法。
----附加信息----
ExtractArrayColumn函数位于此代码下方....以下是如何使用所有这些过程的代码
Private sub RemoveDuplicate()
Dim MyRangeArray As Variant, MyArray As Variant
MyRangeArray = Range("A1:A100").Value
MyArray = ExtractArrayColumn(MyRAngeArray,1)
QuickSort MyArray, LBound(MyArray), UBOUND(MyArray)
MyArray = RemoveDuplicatesBlanks_1DSorted(MyArray)
Range("A1:A100").Value = MyArray
End Sub
Public Function ExtractArrayColumn(Array_Obj As Variant, Column_Index As Long) As Variant
Dim TArr() As Variant
Dim L1 As Long, H1 As Long
Dim i As Long
L1 = LBound(Array_Obj, 1)
H1 = UBound(Array_Obj, 1)
ReDim TArr(L1 To H1)
For i = L1 To H1
TArr(i) = Array_Obj(i, Column_Index)
Next i
ExtractArrayColumn = TArr
End Function