Excel表中查找与删除重复数据方法
方法1
Sub 查找与删除重复数据2()
'查找与删除col列的重复数据
'本例是查找与删除标题为sheet1的EXCEL表中A列（从A2单元格开始）的重复数据Application.ScreenUpdating = False
'可根据实际情况修改下面三行的结尾值
Dim sheetsCaption As String: sheetsCaption = "Sheet1"
Dim Col As String: Col = "C"
Dim StartRow As Integer: StartRow = 4
'以下不需要修改
Dim EndRow As Integer: EndRow = Sheets(sheetsCaption).Range(Col & "65536").End(xlUp).Row
Dim Count_1 As Integer: Count_1 = 0
Dim count_2 As Integer: count_2 = 0
Dim i As Integer: i = StartRow
With Sheets(sheetsCaption)
Do
Count_1 = Count_1 + 1
For j = StartRow To i - 1
If WorksheetFunction.Trim(Format(.Range(Col & i), "<")) = WorksheetFunction.Trim(Format(.Range(Col & j), "<")) Then
Count_1 = Count_1 - 1
'.Range(Col & i).EntireRow.Delete
.Range(Col & i).Select
.Range(Col & i).Font.ColorIndex = 3
.Range(Col & j).Font.ColorIndex = 4
ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 + "[#]"
MsgBox "第" & i & "与第" & j & "条数据重复"
EndRow = Sheets(sheetsCaption).Range(Col & "65536").End(xlUp).Row
i = i - 1
count_2 = count_2 + 1
Exit For
End If
Next
i = i + 1
Loop While i < EndRow + 1
End With
MsgBox "共有" & Count_1 & "条不重复的数据"
MsgBox "删除" & count_2 & "条重复的数据"
Application.ScreenUpdating = True
End Sub
'注：文档中红色行改为：.Range(Col & i).EntireRow.Delete 即可删除所找到的重复数据。（即把整行最前边的“'”去掉）
方法2
Sub tt()
Application.ScreenUpdating = False
Dim a As Integer
Sheets(4).Range("A2:D1000").ClearContents
Sheets(5).Range("A2:D1000").ClearContents
With Sheets(2)
a = .[a65536].End(3).Row
c = 2:
d = 2
For i = 2 To a
b = Application.CountIf(Sheets(1).Range("B:B"), .Cells(i, 2))
If b > 0 Then
.Range("A" & i, "D" & i).Copy Sheets(4).Cells(c, 1)
c = c + 1
Else
.Range("A" & i, "D" & i).Copy Sheets(5).Cells(d, 1)
d = d + 1
End If
Next
End With
Application.ScreenUpdating = True
End Sub
'b=application.countif(sheets(1).Range("A:A"),sheets(2).Cells(i,1))
'countif为统计满足条件的个数,其中sheets(1).Range("A:A")为满足条件的单元格为A列,
'sheets(2).Cells(i,1)为统计条件,其中i为行,1为列,cells为比较函数
'Range("A" & i, "D" & i).Copy Sheets(4).Cells(c, 1)
拷贝A到D列的所有内容至表4
'Sheets(4).Range("A2:C1000").ClearContents
'清除"表4"中"A2:C1000"区域内的所有内容
'特别提示：方法1运行起来较慢，但可以把查找和删除两项功能分开使用。（按方法1中的注示操作即可实现）。方法2运行起来较快，但仅有删除功能，也就是说运行后只能看见你想要的数据，其它被删除的将不会被看见。
