我正在扫描的工作表中的重复项具有不同的列数
我试图通过使用像这样的字符串来指定Range.RemoveDuplicates的列数组:
假设此工作表中有5列
Dim Rng As Range
Dim i As Integer
Dim lColumn As Integer
Dim strColumnArray As String
With ActiveSheet
lColumn = Cells(1, Columns.Count).End(xlToLeft).Column
strColumnArray = "1"
For i = 2 To lColumn
strColumnArray = strColumnArray & ", " & i
Next i
'String ends up as "1, 2, 3, 4, 5"
Set Rng = Range(Cells(1, 1), Cells(1, lColumn).End(xlDown))
Rng.RemoveDuplicates Columns:=Array(strColumnArray), Header:=xlNo
End With
并且我收到运行时错误13 Type Mismatch”错误
因此,我阅读了帖子,并看到有人通过将其指定为这样的数组来在哪里做的:
Dim Rng As Range
Dim i As Integer
Dim lColumn As Integer
Dim strColumnArray() As String
With ActiveSheet
lColumn = Cells(1, Columns.Count).End(xlToLeft).Column
ReDim strColumnArray(lColumn) As String
For i = 1 To lColumn + 1 'The array needs to start with 1
strColumnArray(i) = i
Next i
Set Rng = Range(Cells(1, 1), Cells(1, lColumn).End(xlDown))
Rng.RemoveDuplicates Columns:=strColumnArray, Header:=xlNo
End With
我已经尝试过这种方式:
Rng.RemoveDuplicates(strColumnArray, Header:=xlNo)
这种方式:
Rng.RemoveDuplicates(Columns:=Array(strColumnArray), Header:=xlNo)
以及Variant,String,Integer等的无穷组合。
我必须在这一点上想念它
任何帮助,将不胜感激
我不得不回去重新阅读我的文章。我认为数组必须是一个Variant
。无论如何,这似乎可行:
Sub RemoveDupes()
Dim Rng As Range
Dim i As Integer
Dim lColumn As Integer
Dim ColumnArray As Variant
With ActiveSheet
lColumn = Cells(1, Columns.Count).End(xlToLeft).Column
ReDim ColumnArray(lColumn - 1)
For i = 0 To lColumn - 1 'The array needs to start with 1
ColumnArray(i) = i + 1
Next i
Set Rng = Range(Cells(1, 1), Cells(1, lColumn).End(xlDown))
Rng.RemoveDuplicates Columns:=(ColumnArray), Header:=xlYes
End With
End Sub
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句