如何查找重复的行然后删除其中之一？需要 VBA

How to look for repeated rows and then delete one of them? requires VBA

提问by user1204868

I was wondering if anyone knows how to delete repeated rows.. Say for example,

我想知道是否有人知道如何删除重复的行.. 比如说，

   A        B       C

1  1        3       4
2  2        6       9
3  TEST     1       2
4  TEST     1       2
5  Both     1
6  Hi               2
7  None     3       3
8  Loud     4       4

For the particular example above, TEST was repeated twice.. In some other cases, the name can be some other kinds such as NOON, Morning, etc.. And row 8 does not necessary be the last row. I have no idea of how to compare the rows to check for repeated names and then delete them away. I need to run a macro with this and so i will need VBA. If you know it, please share it with me.. Will be thankful!

对于上面的特定示例，TEST 重复了两次。在其他一些情况下，名称可以是其他类型，例如 NOON、Morning 等。第 8 行不一定是最后一行。我不知道如何比较行以检查重复的名称，然后将它们删除。我需要用这个运行一个宏，所以我需要 VBA。如果你知道，请与我分享..将不胜感激！

Attempt for codes:

尝试代码：

 Sub Macro1()

   Dim LastRow As Long, n As Long, rowstodelete As Long

    LastRow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

      For n = 1 To LastRow

    With Worksheets("Sheet1").Cells(n, 1)
         If .Cells(n, 1) = .Cells(n + 1, 1) Then
           rowstodelete = Worksheets("Sheet1").Cells(n, 1)
           Rows(rowstodelete).Select
           Selection.Delete Shift:=xlUp
         End If
   End With
       Next n

 End Sub

Unfortunately, there were runtime error over at the .Cells(n, 1).. I have no idea why it is.. if you know something can share with me or modify it alittle. will be thankful!

不幸的是，.Cells(n, 1) 出现运行时错误。我不知道为什么会这样......如果你知道什么可以与我分享或稍微修改一下。将不胜感激！

回答by Siddharth Rout

user1204868

用户1204868

It is recommended that when deleting rows, always do it in reverse mode. See this code. Also you do not need to select the cell before deleting. That will slow down your code :)

建议删除行时，始终以反向模式进行。请参阅此代码。您也不需要在删除之前选择单元格。这会减慢你的代码:)

Sub Sample()
    Dim LastRowcheck As Long, n1 As Long

    With Worksheets("Sheet1")
        LastRowcheck = .Range("A" & .Rows.Count).End(xlUp).Row

        For n1 = LastRowcheck To 1 Step -1
            If .Cells(n1, 1).Value = Cells(n1 + 1, 1).Value Then
               .Rows(n1).Delete
            End If
        Next n1
    End With
End Sub

Here is an even better and fasterway.

这是一种更好更快的方法。

Sub Sample()
    Dim LastRowcheck As Long, n1 As Long
    Dim DelRange As Range

    With Worksheets("Sheet1")
        LastRowcheck = .Range("A" & .Rows.Count).End(xlUp).Row

        For n1 = 1 To LastRowcheck
            If .Cells(n1, 1).Value = Cells(n1 + 1, 1).Value Then
                If DelRange Is Nothing Then
                    Set DelRange = .Rows(n1)
                Else
                    Set DelRange = Union(DelRange, .Rows(n1))
                End If
            End If
        Next n1

        If Not DelRange Is Nothing Then DelRange.Delete
    End With
End Sub

FOLLOWUP

跟进

any idea why reverse row deletion is better? – franklin 29 secs ago

知道为什么反向行删除更好吗？– 富兰克林 29 秒前

When you delete a row, your Forloop messes up as you are targeting set number of rows. You have to then write extra line of code , as you did, to keep track of the rows that you deleted. It also slows down your code :) When you delete in reverse then you don't have to account for the deleted row as it falls out of the currentrunning loop. This way your code is faster. But then like I mentioned above, if you are not using reverse row deletion then use the 2nd code that I gave. That is even faster.

当您删除一行时，您的For循环会因您的目标行数而混乱。然后您必须像您一样编写额外的代码行，以跟踪您删除的行。它也会减慢您的代码 :) 当您反向删除时，您不必考虑已删除的行，因为它脱离了当前的运行循环。这样你的代码会更快。但是就像我上面提到的那样，如果您不使用反向行删除，请使用我提供的第二个代码。那甚至更快。

One point that I would like to mention though. If you are using Excel 2007/2010 then the one line code that @brettdj suggested is the fastest :)

有一点我想提一下。如果您使用的是 Excel 2007/2010，那么@brettdj 建议的一行代码是最快的:)

HTH

HTH

Sid

锡德

回答by brettdj

Manual
Bill Jelen's websiteoffers three non-VBA techniques

手册
Bill Jelen 的网站提供了三种非 VBA 技术

1. All Versions:Use the Unique Option in Advanced Filter
2. Xl 07/10:Use Conditional Formatting to Mark Duplicates
3. Xl 07/10:Use Remove Duplicates icon

1. 所有版本：使用高级过滤器中的唯一选项
2. Xl 07/10：使用条件格式标记重复项
3. Xl 07/10：使用删除重复项图标

For (3) the equivalent VBA would be something like this (for no headers)
ActiveSheet.Range("$A$1:$C$100").RemoveDuplicates Columns:=1, Header:=xlNo

对于 (3) 等效的 VBA 将是这样的（对于没有标题）
ActiveSheet.Range("$A$1:$C$100").RemoveDuplicates Columns:=1, Header:=xlNo

Handling existing duplicates
My free Duplicate Master addinwill let you

处理现有的重复
我的免费Duplicate Master 插件会让你

• Select
• Colour
• List
• Delete

• 选择
• 颜色
• 列表
• 删除

duplicates on either cells, entire rows (which appears to be your question) or certain columns in a row

在单元格、整行（这似乎是您的问题）或一行中的某些列上重复

But more importantly it will let you run more complex matching than exact strings, ie

但更重要的是它会让你运行比精确字符串更复杂的匹配，即

• Case Insensitive / Case Sensitive searches
• Trim/Clean data
• Remove all blank spaces (including CHAR(160))
• Run regular expression matches
• Match on any combination of columns (ie Column A, all columns, Column A&B etc)

• 不区分大小写/区分大小写的搜索
• 修剪/清理数据
• 删除所有空格（包括 CHAR(160)）
• 运行正则表达式匹配
• 匹配列的任意组合（即 A 列、所有列、A 列和 B 列等）

回答by user1204868

I have attempted my code once again and it can work out well.. Thanks! I will share it here to answer similar questions in the future!

我再次尝试了我的代码，它可以很好地工作..谢谢！以后我会在这里分享来回答类似的问题！

  Sub Macro1()

  Dim LastRowcheck As Long, n1 As Long, rowschecktodelete As Long

  LastRowcheck = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

  For n1 = 1 To LastRowcheck
    With Worksheets("Sheet1").Cells(n1, 1)
      If Cells(n1, 1) = Cells(n1 + 1, 1) Then
        Worksheets("Sheet1").Cells(n1, 1).Select
        Selection.EntireRow.Delete
     End If
   End With
  Next n1

  End Sub

回答by Peter Albert

Easiest way to do this in VBA (in 2007 or higher):

在 VBA 中执行此操作的最简单方法（2007 年或更高版本）：

Worksheet("Sheet1").Range("A1").CurrentRegion.RemoveDuplicates(Array(1, 2, 3))

Worksheet("Sheet1").Range("A1").CurrentRegion.RemoveDuplicates(Array(1, 2, 3))

Depending on your worksheet layout you might need to adjust Range("A1").CurrentRegionto your data range...

根据您的工作表布局，您可能需要调整Range("A1").CurrentRegion到您的数据范围...

回答by Guest

I think we need to sort the data first before running this macro to remove the duplicates completely.

我认为我们需要先对数据进行排序，然后再运行此宏以完全删除重复项。