vba 如何添加VBA代码以从excel中删除重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24763327/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add a VBA code to remove duplicates from excel
提问by Paulo
I've 2 sheets, for example (sheet 1 and 2). I'll copy some data from sheet 2 to sheet 1.
例如，我有 2 张纸（sheet 1 和 2）。我会将一些数据从工作表 2 复制到工作表 1。
After that i need to remove duplicated values from a column.
之后，我需要从列中删除重复的值。
My code is:
我的代码是：
Sub Button1_Click()
Dim excel As excel.Application
Dim wb As excel.Workbook
Dim sht As excel.Worksheet
Dim f As Object
Set f = Application.FileDialog(3)
f.AllowMultiSelect = False
f.Show
Set excel = CreateObject("excel.Application")
Set wb = excel.Workbooks.Open(f.SelectedItems(1))
Set sht = wb.Worksheets("Query1")
'(((((select sheet 2)))))
'((((((选择工作表 2)))))
sht.Activate
sht.Columns("A:D").Copy '(((((copy from sheet2))))
Range("I5").PasteSpecial Paste:=xlPasteValues '(((((paste in sheet1))))
sht.Activate
sht.Columns("F:H").Copy '(((((copy from sheet2))))
Range("Q5").PasteSpecial Paste:=xlPasteValues '(((((paste in sheet1))))
wb.Close
End Sub
I need to know the code and location to remove the duplicated values from the column B - sheet1for example.
例如，我需要知道从B 列中删除重复值的代码和位置- sheet1。
thks
谢谢
回答by Isaac G Sivaa
Please try with this.
Sheets(1).Range(Range("B1"), Range("B1").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo.
请试试这个。
Sheets(1).Range(Range("B1"), Range("B1").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo.
回答by Alter
If you want the overall file to be the same (duplicate values are removed, but the positions of remaining values are not changed) then this code will work:
如果您希望整个文件相同（删除重复值，但不更改剩余值的位置），则此代码将起作用：
Note: you will need to add a reference to Microsoft Scripting Runtime to use the dictionary object
注意：您需要添加对 Microsoft Scripting Runtime 的引用才能使用字典对象
Sub removeDuplicatesFromColumn(columnIndex As Integer)
On Error GoTo ErrorHandler
Dim rowIndex As Integer
Dim columnValues As Dictionary
Set columnValues = New Dictionary
'I've set this up backwards in case you want to remove rows/cells that are duplicates
For rowIndex = 1 To ActiveSheet.UsedRange.Rows.Count
If columnValues.Exists(Cells(rowIndex, columnIndex).Value) Then
Cells(rowIndex, columnIndex).Value = ""
Else
columnValues.Add Cells(rowIndex, columnIndex).Value, ""
End If
Next rowIndex
Exit Sub
ErrorHandler:
MsgBox Err.Description
'resume
End Sub
