将最后一行值范围与 VBA 中的一组行进行比较 [英] Compare last row range of values to a set of rows in VBA

问题描述

我基本上是从一个单元格输入数据并将其复制到另一组单元格，如下表所示.现在我需要检查输入数据是否与之前的数据相同.如果相同，我将清除该行并将其从表格中删除.

I am basically inputting data from a cell and copying it to another set of cells as seen on the table below as an example. Now I need to check if the input data is the same as the previous ones. If it is the same, I will clear that row and remove it form the table.

DATA1 DATA2 DATA3
cat    1    white
dog    2    white
dog    1    brown
cat    1   white (should be compared and removed from table - similar with 1st row)

我尝试过使用 For 循环函数.但是，范围值不能接受 Range("S" & Lastrow & ":" & "X" & Lastrow).你能建议什么应该是正确的格式吗?谢谢！

I have tried using a For loop function. However, Range value cannot accept Range("S" & Lastrow & ":" & "X" & Lastrow). Could you please advise what should be the correct format for this? Thank you!

Sub RowCompare()

    Dim ary1() As Variant
    Dim Range1 As Range, Range2 As Range, rr1 As Range, rr2 As Range

    Set xWs = ThisWorkbook.Sheets("Summary")
    LastRow = xWs.Range("T" & Rows.Count).End(xlUp).Row + 1 

    'Check last row with previous rows
    Set Range1 = Range("S5:X" & LastRow)

    For i = LastRow - 1 To 2 Step -1
        Set Range2 = Range("S")
        Set rr1 = Range1.Rows(1)
        Set rr2 = Range2.Rows(1)
        ary1 = Application.Transpose(Application.Transpose(rr1))
        ary2 = Application.Transpose(Application.Transpose(rr2))
        st1 = Join(ary1, ",")
        st2 = Join(ary2, ",")
        If st1 = st2 Then
            MsgBox "UPH already plotted"
            Exit Sub
        End If
    Next

End Sub

推荐答案

例如公式

=COUNTIFS(A:A,A:A,B:B,B:B,C:C,C:C)

结果为 >1 如果当前行是重复的:

results in >1 if the current row is a duplicate:

你也可以在 VBA 中使用它

You could also use this in VBA

Option Explicit

Public Sub TestIfLastRowIsDuplicate()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    Dim LastRow As Long 'find last used row
    LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Dim Cnt As Long 'count how many duplicates of last row exist
    Cnt = WorksheetFunction.CountIfs(ws.Range("A:A"), ws.Range("A" & LastRow), _
                                     ws.Range("B:B"), ws.Range("B" & LastRow), _
                                     ws.Range("C:C"), ws.Range("C" & LastRow))

    If Cnt > 1 Then
        MsgBox "Last row is duplicate"
    Else
        MsgBox "Last row is unique"
    End If
End Sub

这篇关于将最后一行值范围与 VBA 中的一组行进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！