vba 从数组中删除行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21483912/
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
Delete Row from Array
提问by CongdonJL
I am trying to go through an array to find duplicate entries in a single column of that array and delete the entire row.
我试图通过一个数组在该数组的单列中查找重复条目并删除整行。
I am getting figuring out rangeStart, rangeEnd, and lastrowabove this and that part is working fine.
我越来越搞清楚rangeStart，rangeEnd和lastrow上面这一点，这部分工作正常。
data = Range(rangeStart, rangeEnd)
For i = lastrow - 1 To 2 Step -1
If data(i - 1, x) = data(i, x) Then
'Delete data(i)
End If
Next
Any help with this would be awesome!
对此的任何帮助都会很棒！
回答by Tim Williams
Sub RemoveDups()
Const COMPARE_COL as Long = 1
Dim a, aNew(), nr As Long, nc As Long
Dim r As Long, c As Long, rNew As Long
Dim v As String, tmp
a = Selection.Value
nr = UBound(a, 1)
nc = UBound(a, 2)
ReDim aNew(1 To nr, 1 To nc)
rNew = 0
v = Chr(0)
For r = 1 To nr
tmp = a(r, COMPARE_COL)
If tmp <> v Then
rNew = rNew + 1
For c = 1 To nc
aNew(rNew, c) = a(r, c)
Next c
v = tmp
End If
Next r
Selection.Value = aNew
End Sub
回答by hstay
Does this help?:
这有帮助吗？：
If data(i - 1, x) = data(i, x) Then
data(i,x).EntireRow.Delete
End If
回答by brettdj
Why not use Excel's inbuilt Unique options (Data ... Remove Duplicates)?
为什么不使用 Excel 的内置 Unique 选项（Data ... Remove Duplicates）？
Another efficient VBAmethod is to use a Dictionary.
另一种有效的VBA方法是使用Dictionary.
Sub A_Unique_B()
Dim X
Dim objDict As Object
Dim lngRow As Long
Set objDict = CreateObject("Scripting.Dictionary")
X = Application.Transpose(Range([a1], Cells(Rows.Count, "A").End(xlUp)))
For lngRow = 1 To UBound(X, 1)
objDict(X(lngRow)) = 1
Next
Range("B1:B" & objDict.Count) = Application.Transpose(objDict.Keys)
End Sub
