检查所有列后删除重复的行 [英] Removing duplicate rows after checking all columns

问题描述

我已经写下面的宏来检查所有的列后删除重复的行。我认为这是正确的，基于逻辑，但输出是不正确的，应该显示的几行被删除。有人可以帮我吗？

I have written the below macro to remove duplicate rows after checking all columns. I thought it was working correctly, based on the logic, but the output is not correct, and a few of the rows that should be showing up are being deleted. Can anyone help me with that?

在此宏运行之前，我有另一个宏可以通过几列对数据进行排序，以确保类似的行分组在一起，因此宏可以将其帐户，只检查上面的行，而不是检查所有行？非常感谢任何帮助！

Before this macro is run, i have another macro to sort the data by a few columns to ensure that similar rows are grouped together, so maybe the macro can take that into account, and check only the row above it instead of checking all the rows? Greatly appreciate any help!

Sub Delete_Repeated_Rows()
Dim Rng As Range
Dim ColumnCounter As Integer

Set Rng = ActiveSheet.UsedRange.Rows

'Using ColumnCounter to hold total number of cells that match. If all of them match, delete row'

 For r = Rng.Rows.Count To 1 Step -1
    ColumnCounter = 0
    For Col = Rng.Columns.Count To 1 Step -1 'Loop through columns and find matches'
        If Application.WorksheetFunction.CountIf(Rng.Columns(Col), Rng.Cells(r, Col)) > 1 Then
            ColumnCounter = ColumnCounter + 1
        End If
    Next Col

    If ColumnCounter = Rng.Columns.Count Then
        Rng.Rows(r).EntireRow.Delete
    End If
Next r
End Sub

推荐答案

要删除副本，您可以点击数据功能区>数据工具上的删除重复按钮。



以下是演示：

我在工作表中有这样的数据：


我想在列A中拥有唯一的数据

点击删除重复按钮，并提示以下屏幕提示，我取消选中columnB



单击确定按钮，并有一个通知框告诉我这2个重复值已被删除，并找到5个唯一值作为以下屏幕：




以下是最终结果：

To delete the duplicate you can just click the "Remove duplicate" button at Data Ribbon > Data Tools.
Following is the demo:
I have the data like this in the worksheet:

I would like to have unique data at column A
click the "Remove duplicate" button and following screen prompted out and i uncheck columnB

Click the OK button and there is a notification box telling me that 2 duplicated value been remove and 5 unique value were found as following screen:

The following is the end result:

这篇关于检查所有列后删除重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！