如果 Excel 2007 vba 中的第 R 列行中有 2 个值，则删除重复的行

发布于 2024-11-29 22:09:52 字数 467 浏览 0 评论 0原文

ActiveSheet.Range(cells("$2", "$A"), cells("$" & CStr(mowz), "$Q"))._
RemoveDuplicates                         
Columns:=Array(1, 2, 6, 7, 8, 9), Header:=xlYes

这是我记录的宏，并使用它来删除 excel 2007 vba 中的重复项。但是我有一个新任务要解决。那就是我必须删除重复的行，当且仅当其“Rth”列的值为 2 时，否则即使它是重复的，也不应该删除它

有没有办法将条件放入重复行宏中？请告诉我。任何建议都被接受

在我的工作表中，我有 16 列，上面的宏如果第 1,2,6,7,8,9 列具有相同的值，则删除重复项，但问题是，如果它包含所有列，则必须删除它6 列重复，并且第 R 列中还有一个“2”值，如果第 R 列具有其他值，则不应删除，即使所有六列都相同。

原文

ActiveSheet.Range(cells("$2", "$A"), cells("$" & CStr(mowz), "$Q"))._
RemoveDuplicates                         
Columns:=Array(1, 2, 6, 7, 8, 9), Header:=xlYes

This the Macro, I recorded and using it to delete duplicates in excel 2007 vba.But I got a new task to solve.That is I have to remove the duplicated rows,if and only if its "Rth" Column has value 2 in it, else it should not delete it even though it is a duplicate

Is there any way to put a condition into the duplicate rows macro?Please let me know. And any suugestions are accepted

In my sheet I have 16 columns and The above macro Deletes the duplicates if Columns 1,2,6,7,8,9 has same values in it but the thing is, It must delete it if it has all the 6 columns duplicated and also a "2" value in its Rth column and it should not delete if Rth column has someother value even though all the six columns are same.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

吃素的狼 2024-12-06 22:09:52

我不知道你的其他代码，所以我无法集成它，但这里有一个 sub() ，它将遍历你的 R 列，如果它在里面找到“2”，它将删除整行。您始终可以通过添加“Call Delete2s”将其添加到其他代码中（“call”是可选的，但我倾向于包含它）。

Sub Delete2s()
Range(Cells(1, "R"), Cells(Rows.Count, "R").End(xlUp)).Select

Dim lastRow As Long
lastRow = ActiveSheet.UsedRange.Rows.Count

For i = lastRow To 1 Step -1
    If Selection.Rows(i).Value = 2 Then
        Selection.Rows(i).EntireRow.Delete
    End If
Next 
End Sub

工作原理：它找到 R 列中使用的最后一个单元格（您可以调整它），然后向后循环（您需要在删除单元格时执行此操作，否则会弄乱环形）。如果值为 2，则会删除整行！

I don't know about your other code so I can't integrate this, but here is a sub() that will go through your R column and if it finds a "2" inside, it will delete the entire row. You can always add this to your other code by adding "Call Delete2s" to it ("call" is optional, but I tend to include it).

Sub Delete2s()
Range(Cells(1, "R"), Cells(Rows.Count, "R").End(xlUp)).Select

Dim lastRow As Long
lastRow = ActiveSheet.UsedRange.Rows.Count

For i = lastRow To 1 Step -1
    If Selection.Rows(i).Value = 2 Then
        Selection.Rows(i).EntireRow.Delete
    End If
Next 
End Sub

How it works: It finds the last cell used in column R (you can adjust this) and then loops through it backwards (you need to do this when you delete cells, otherwise you'll mess up the loop). If the value is 2, it deletes the entire row!

回复收藏 0 原文

~没有更多了~