如何查找重复的行，然后删除其中的一个？需要VBA [英] How to look for repeated rows and then delete one of them? requires VBA

问题描述

我想知道有没有人知道如何删除重复的行..举个例子，例如

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

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

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!

尝试代码：

 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

不幸的是，在.Cells（n，1）有运行时错误..我不知道为什么。如果你知道有什么可以与我分享或修改它的话。将感谢！

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!

推荐答案

user1204868

user1204868

建议删除行，总是在反向模式下执行。看这个代码。此外，您不需要在删除之前选择单元格。这将减慢你的代码：）

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

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

更好？ - franklin 29 secs ago

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

当你删除一行，您的对于循环混乱，因为您定位设置的行数。您必须像您一样编写额外的代码行来跟踪您删除的行。它也减慢了你的代码:)当你反过来删除你不必考虑删除的行，因为它不在当前的运行循环。这样你的代码更快。但是像上面提到的那样，如果你不使用反向行删除，那么使用我给出的第二个代码。这更快。

When you delete a row, your For loop 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 current running 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.

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

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 :)

HTH

Sid

这篇关于如何查找重复的行，然后删除其中的一个？需要VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！