Excel / VBA通​​过交叉引用2个不同的表，然后删除1行来删除重复的行 [英] Excel / VBA Remove duplicate rows by cross referencing 2 different sheets then deleting 1 row

问题描述

我有两张单独的表格，可以将它们称为表A，表B。我在表B中也有表B中的数据。我想找到相等的行，并从表B中删除它们。

我无法组合2张表并使用过滤器，因为我正在进行动态SQL来查询不同的数据。

每张表都有一个唯一的键列

我可以使用VBA建议和Excel公式。只要我不组合表格。

感谢这么多人！

对不起，显然我犯了一个错误。这里有一个无限循环。这是Ben的答案btw。我只是转发了一个可编译的版本。

  Sub CleanDupes（）
 Dim wsA As Worksheet 
 Dim wsB As工作表
 Dim keyColA As String 
 Dim keyColB As String 
 Dim rngA As Range 
 Dim rngB As Range 
 Dim intRowCounterA As Integer 
 Dim intRowCounterB As Integer 
 
 
 keyColA =A
 keyColB =A
 
 intRowCounterA = 1 
 intRowCounterB = 1 
 
设置wsA =工作表（Sheet2）
设置wsB =工作表（Sheet1）
 
尽管不是IsEmpty（wsA.Range（keyColA& intRowCounterA）.Value）
 
 
设置rngA = wsA.Range（keyColA& intRowCounterA）
 
 intRowCounterB = 1 
尽管不是IsEmpty（wsB.Range（keyColB& intRowCounterB ）.Value）
 
设置rngB = wsB.Range（keyColB& intRowCounterB）
 
如果rngA.Value = rngB.Value然后
 
行（intRowCounter B）.EntireRow.Delete 
 intRowCounterB = intRowCounterB  -  1 
 
 
 End If 
 intRowCounterB = intRowCounterB + 1 
循环
 intRowCounterA = intRowCounterA + 1 
循环
 End Sub 
  

解决方案

pre> Sub CleanDupes（）
Dim wsA As Worksheet
Dim wsB As Worksheet
Dim keyColA As String
Dim keyColB As String
Dim rngA As Range
Dim rngB As Range
Dim intRowCounterA As Integer
Dim intRowCounterB As Integer
Dim strValueA As String


keyColA =A
keyColB =B

intRowCounterA = 1
intRowCounterB = 1

设置wsA =工作表（Sheet A）
设置wsB =工作表（Sheet B）

尽管不是IsEmpty（wsA.Range（keyColA& intRowCounterA）.Value）
intRowCounterB = 1
设置rngA = wsA.Range（keyColA& intRowCounterA）
strValueA = rngA.Value
尽管不是IsEmpty（wsB.Range keyColB& intRowCounterB）.Value
设置rngB = wsB.Range（keyColB& intRowCounterB）
如果strValueA = rngB.Value然后
'删除行的代码在这里，但我是不确定'
'是什么。'
wsB.Rows（intRowCounterB）.Delete
intRowCounterB = intRowCounterB - 1
End If
intRowCounterB = intRowCounterB + 1
循环
intRowCounterA = intRowCounterA + 1
循环
结束子


这应该让你开始。

I have 2 separate sheets, lets call them sheet A, sheet B. I have data in sheet B which is also in sheet A. I want to find those rows that are equal and remove them from sheet B.

I cannot combine the 2 sheets and use filters because I'm doing dynamic SQL to query different data.

Each sheet has a unique key column

I'm ok with VBA suggestions and Excel formulas. Just as long as I don't combine sheets.

Thank so much guys!

Sorry, apparently I made a mistake. There is an infinite loop here somewhere. This is Ben's answer btw. I just reposted a compilable version.

    Sub CleanDupes()
    Dim wsA As Worksheet
    Dim wsB As Worksheet
    Dim keyColA As String
    Dim keyColB As String
    Dim rngA As Range
    Dim rngB As Range
    Dim intRowCounterA As Integer
    Dim intRowCounterB As Integer


    keyColA = "A"
    keyColB = "A"

    intRowCounterA = 1
    intRowCounterB = 1

    Set wsA = Worksheets("Sheet2")
    Set wsB = Worksheets("Sheet1")

    Do While Not IsEmpty(wsA.Range(keyColA & intRowCounterA).Value)


        Set rngA = wsA.Range(keyColA & intRowCounterA)

         intRowCounterB = 1
        Do While Not IsEmpty(wsB.Range(keyColB & intRowCounterB).Value)

            Set rngB = wsB.Range(keyColB & intRowCounterB)

            If rngA.Value = rngB.Value Then

                 Rows(intRowCounterB).EntireRow.Delete
                 intRowCounterB = intRowCounterB - 1


            End If
              intRowCounterB = intRowCounterB + 1
        Loop
        intRowCounterA = intRowCounterA + 1
    Loop
End Sub

解决方案

Sub CleanDupes()
    Dim wsA As Worksheet
    Dim wsB As Worksheet
    Dim keyColA As String
    Dim keyColB As String
    Dim rngA As Range
    Dim rngB As Range
    Dim intRowCounterA As Integer
    Dim intRowCounterB As Integer
    Dim strValueA As String


    keyColA = "A"
    keyColB = "B"

    intRowCounterA = 1
    intRowCounterB = 1

    Set wsA = Worksheets("Sheet A")
    Set wsB = Worksheets("Sheet B")

    Do While Not IsEmpty(wsA.Range(keyColA & intRowCounterA).Value)
        intRowCounterB = 1
        Set rngA = wsA.Range(keyColA & intRowCounterA)
        strValueA = rngA.Value
        Do While Not IsEmpty(wsB.Range(keyColB & intRowCounterB).Value
            Set rngB = wsB.Range(keyColB & intRowCounterB)
            If strValueA = rngB.Value Then
                 'Code to delete row goes here, but I'm not sure exactly'
                 'what it is.'
                 wsB.Rows(intRowCounterB).Delete
                 intRowCounterB = intRowCounterB - 1
            End If
            intRowCounterB = intRowCounterB + 1
        Loop
        intRowCounterA = intRowCounterA + 1
    Loop
End Sub

That should get you started.

这篇关于Excel / VBA通​​过交叉引用2个不同的表，然后删除1行来删除重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！