如何识别两列中具有相同值的行?

本文介绍了如何识别两列中具有相同值的行?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

如果B列 AND 的C列使用VBA脚本具有重复的值，我正在尝试标识整行.例如，如果我有一个包含以下内容的表:

I'm trying to identify an entire row if column B AND column C have repeating values with a VBA script. For example if I have a table that contains:

ID Age Grade 1 14 90 2 15 78 3 14 90 4 16 86 5 16 86 6 15 89 7 14 88

运行脚本后，我想要一个新的工作表，其中C列的 AND 列C中的行具有重复的值.因此，新工作表应如下所示:

After I run the script, I want a new sheet with the rows that have repeating values in column B AND column C. So the new sheet would look like:

ID Age Grade 1 14 90 3 14 90 4 16 86 5 16 86

到目前为止，这是我确定行的方式.我还没走那么远.

This is what I have so far in terms of identifying the rows. I'm not that far along.

Sub ID() Dim LastRowcheck As Long, n1 As Long Dim LastRowcheck2 As Long, n2 As Long With Worksheets("grades") LastRowcheck = .Range("B" & .Rows.Count).End(xlUp).Row LastRowcheck = .Range("C" & .Rows.Count).End(xlUp).Row For n1 = LastRowcheck To 1 Step -1 If .Cells(n1, 1).Value = Cells(n1 + 1, 1).Value And .Cells(n2, 1).Value = Cells(n2 + 1, 1).Value Then '''export to new sheet End If Next n1 End With End Sub

推荐答案

使用application.countifs标识倍数.

Dim lastRowcheck As Long, n1 As Long With Worksheets("grades") lastRowcheck = Application.Max(.Range("B" & .Rows.Count).End(xlUp).Row, _ .Range("C" & .Rows.Count).End(xlUp).Row) For n1 = lastRowcheck To 1 Step -1 If Application.CountIfs(.Columns("B"), .Cells(n1, "B").Value2, .Columns("C"), .Cells(n1, "C").Value2) > 1 Then '''export to new sheet Debug.Print .Cells(n1, "A") & ":" & .Cells(n1, "B") & ":" & .Cells(n1, "C") End If Next n1 End With