vba Visual Basic Excel - 删除行的宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10315120/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Visual Basic Excel - Macro to delete row
提问by mannge
I have 2 sheets (with phone numbers) in my document. if the number exists in sheet1 i want to remove the row from sheet 2.
我的文档中有 2 张纸（带有电话号码）。如果工作表 1 中存在该数字，我想从工作表 2 中删除该行。
I'm almost there (This is the first time I am using VBA). But could anyone help me with the last part.
我快到了（这是我第一次使用 VBA）。但是谁能帮我完成最后一部分。
Sub CleanList()
Dim stopList As Range, cell1 As Range
Set stopList = Sheet1.Range("A1:A10000")
For Each cell1 In stopList
Dim fullList As Range, cell2 As Range
Set fullList = Sheet2.Range("A2:A10000")
For Each cell2 In fullList
If NumberFix(cell1.Value) = NumberFix(cell2.Value) Then
cell2.EntireRow.Delete
End If
Next cell2
Next cell1
End Sub
Private Function NumberFix(ByVal nr As String) As String
If Not nr.StartsWith("46") Then
nr = "46" + nr
End If
NumberFix = nr
End Function
回答by Brad
The first thing is the way your using nr.StartsWithis more VB.NET-esque. THe function your looking for in VBA (probably not VB-script btw) is
第一件事是您的使用nr.StartsWith方式更像 VB.NET。您在 VBA 中寻找的功能（可能不是 VB 脚本顺便说一句）是
Dim firstTwoChar As String
firstTwoChar = Mid(nr, 1, 2)
If Not firstTwoChar = "46" Then
nr = "46" + nr
End If
NumberFix = nr
But even with that I'd say you should not be using a for...eachiterator if you are deleting rows. The problem is when you delete row 5 then row 6 becomes row 5 and the next row you go to is row "6" but is actually row 7 in the original list, effectively skipping the original row 6.
但即便如此，我还是会说，for...each如果您要删除行，则不应使用迭代器。问题是当您删除第 5 行时，第 6 行变为第 5 行，您转到的下一行是“6”行，但实际上是原始列表中的第 7 行，从而有效地跳过了原始第 6 行。
You need to move backwards. Something like
你需要向后移动。就像是
Sub CleanList()
Dim stopList As Range, cell1 As Range
Set stopList = Sheet1.Range("A1:A10000")
For Each cell1 In stopList
Dim fullList As Range, cell2 As Range
Dim firstRowSheet2 As Integer, lastRowSheet2 As Integer, r As Integer
Dim sheet1sNumber As String
sheet1sNumber = NumberFix(cell1.Value) 'you really only need to do this once
so you may as well bring it out of
the for loop and store the value and
not recalculate each time
Dim cell2 As Range
For r = firstRowSheet2 To lastRowSheet2 Step -1
'"Step -1" allows you to move backwards through the loop
With Sheet2
Set cell2 = .Cells(r, 1)
If sheet1sNumber = NumberFix(cell2.Value) Then
cell2.EntireRow.Delete
End If
End With
Next r
Next cell1
End Sub
But of course @ExternalUse is right. There are a lot of built in options for removing duplicates from a list. Unless you are trying to learn VBA, then this is a good exercise.
但当然@ExternalUse 是对的。有很多内置选项可用于从列表中删除重复项。除非您正在尝试学习 VBA，否则这是一个很好的练习。
