excel - 将值的最后一行范围与 VBA 中的一组行进行比较
问题描述
我基本上是从一个单元格输入数据并将其复制到另一组单元格中,如下表所示。现在我需要检查输入数据是否与之前的相同。如果相同,我将清除该行并将其从表中删除。
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)
。您能否告知正确的格式应该是什么?谢谢!
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
当前行是否重复:
您也可以在 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
推荐阅读
- r - For循环但跳过行
- php - 如何以编程方式将块添加到树枝模板?
- python - 扩展多项式以拟合数据:创建任意数量的函数参数
- javascript - 如何设置获取数据的默认值
- awk - 比较两列:如果匹配,则在新列中打印值,如果不匹配,则将第二列的值打印到新列
- docusignapi - DocuSign 签名组从一个帐户导入到另一个帐户“相同的签名组 ID”
- c# - 从 DJ Decks(先锋)读取输入
- r - 查找 FaceValue 5 天的百分比增长率
- javascript - 您如何使用不同的数据库(Firestore、Auth、Realtime)构建多功能批量写入?
- php - 我可以在 mysql 事务中添加一个查询数据库并使用其结果的函数吗?