Excel中的行比较

Question

我遇到了一个问题，因为我编写了一个宏，用于比较工作表中的行并突出显示重复的行。但是，当记录数量较多时，它需要更长的时间来完成它的操作。当比较开始时，它将拾取第一条记录，将其与所有剩余记录进行比较，并突出显示是否存在重复记录，然后移动到第二条记录，此过程一直持续到最后一条记录。

有人能告诉我一个更好的解决方案吗？

这是我的代码；

RowCount = ActiveSheet.UsedRange.Rows.Count
ColumnCount = ActiveSheet.UsedRange.Columns.Count
For frownum = 1 To RowCount
    For rownum = 1 To RowCount
        RecFound = 0
                For colnum = 1 To ColumnCount
                        If frownum <> rownum Then
                            If ActiveSheet.Cells(frownum, colnum).Value = ActiveSheet.Cells (rownum, colnum).Value Then
                              RecFound = RecFound + 1
                            End If
                        End If
                Next colnum

        If ColumnCount = RecFound Then
             For errRow = 1 To ColumnCount
                  ThisWorkbook.Worksheets("RowCompare").Cells(frownum, errRow).Interior.Color = RGB(251, 231, 128)
             Next errRow
        End If
     Next rownum
Next frownum

Answer 1

Sub test2()
    Dim rowCount As Long
    Dim columnCount As Long

    '//You need "Microsoft Scripting Runtime" library for this to work
    '//You can add this library by going Tools -> References -> Browse...
    '//Find "scrrun.dll" file in your System32 folder
    Dim dict As Scripting.Dictionary
    Set dict = New Scripting.Dictionary

    Dim ws As Worksheet
    Set ws = activesheet

    With ws
        rowCount = .UsedRange.Rows.Count
        columnCount = .UsedRange.Columns.Count

        Dim i  As Long
        For i = 1 To rowCount
            Dim rng As Range
            Dim joinedRow As String
            Set rng = Range(.Cells(i, 1), .Cells(i, columnCount))
            joinedRow = Join(Application.Transpose(Application.Transpose(rng)), Chr(0))

            If dict.Exists(joinedRow) Then
                rng.Interior.Color = RGB(251, 231, 128)
            Else
                dict.Add joinedRow, 1
            End If
        Next i
    End With
End Sub

改进的想法，在这里使用:根据您的情况，添加Scripting.Dictionary类的How to compare two entire rows in a sheet。

希望它能起作用。