VBA当列X的值与列Y的值不匹配时，如何删除行 [英] VBA How to Delete Rows when value of Column X does not match value of column Y

问题描述

我正在尝试删除列x的单元格值与列y的单元格值不匹配的每一行。我不断得到一个对象错误，我很确定我缺少一些明显的东西。

  Sub Gooddata（）
 
 Application.ScreenUpdating = False 
 
 lr = Range（C65536）。End（xlUp）.Row 
对于a = lr至1步骤-1 
如果Cells（a，8）.Value&单元格（a，9）.Value然后
单元格（a，3）。选择
 ActiveCell.EntireRow.Delete = True 
结束如果
下一个
 
 Application.ScreenUpdating = True 
 End Sub 
  

编辑：我已经对代码基于这里的意见建议，输入图像的代码和输出图像。

  Sub Gooddata（）
 
 Application.ScreenUpdating = False 
 
 lr = Range（C65536）。End（xlUp）.Row 
 For a = lr To 1步骤-1 
如果单元格（a，8）.Value<>单元格（a，9）.Value然后
单元格（a，3）.EntireRow.Delete 
结束如果
下一个
 
 Application.ScreenUpdating = True 
 End Sub 
  

如果以我想要的方式执行代码到Old Argus Building 12不会被删除，但它下面的行将由于逗号和破折号。

之后：

I'm trying to delete every row where the cell value for column x does not match the cell value of column y. I keep getting an object error and I'm pretty sure I'm missing something obvious.

Sub Gooddata()

Application.ScreenUpdating = False

lr = Range("C65536").End(xlUp).Row
For a = lr To 1 Step -1
If Cells(a, 8).Value <> Cells(a, 9).Value Then
Cells(a, 3).Select
ActiveCell.EntireRow.Delete = True
End If
Next a

Application.ScreenUpdating = True
End Sub

Edit: I have made edits to the code based on the suggestions of the comments here's the input image the code and the output image.

Sub Gooddata()

Application.ScreenUpdating = False

lr = Range("C65536").End(xlUp).Row
For a = lr To 1 Step -1
If Cells(a, 8).Value <> Cells(a, 9).Value Then
Cells(a, 3).EntireRow.Delete
End If
Next a

Application.ScreenUpdating = True
End Sub

If the code executed in the way that I want it to Old Argus Building 12 will not be deleted however the row below it will because of the comma and dash.

解决方案

Try this. This could break if second column has more words than first XD

Sub Gooddata()

Dim lr As Long
Dim arr As Variant
Dim brr As Variant

Application.ScreenUpdating = False

With ActiveSheet

lr = .Cells(.Rows.count, "G").End(xlUp).Row

For A = lr To 1 Step -1

    I = 0
    arr = Split(Trim(.Cells(A, 7).Value2), " ")
    brr = Split(Trim(.Cells(A, 8).Value2), " ")

    For Each e In arr

        '.Cells(A, 9) = e
        If e <> brr(I) Then
            .Cells(A, 3).EntireRow.Delete
            Exit For
            'GoTo yarr
        End If
        I = I + 1

    Next e

'yarr:
'Erase arr
'Erase brr

Next A

End With

Application.ScreenUpdating = True

End Sub

Before:

After:

这篇关于VBA当列X的值与列Y的值不匹配时，如何删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！