需要对下面的代码进行一些更改
问题描述:
这是由一位程序员Reafidy在*上建议的代码。它按预期工作。 Need a better optimized code? 现在我必须重新使用相同的代码来处理大文件。需要对下面的代码进行一些更改
Sub Delete_Duplicate_Codes()
ThisWorkbook.Worksheets("Data").Activate
Dim vData As Variant, vArray As Variant
Dim lRow As Long
With ActiveSheet.Range("A3", Cells(Rows.Count, "A").End(xlUp)).Offset(, 52)
.FormulaR1C1 = "=RC[-24]&RC[-23]&RC[-19]&RC[-18]&RC[-17]&RC[-16]" ' I know what these meant concatenate A,B,F,G,H,I and I have changed it accordingly
vData = .Resize(, 1).Value
End With
ReDim vArray(1 To UBound(vData, 1), 0)
With CreateObject("Scripting.Dictionary")
For lRow = 1 To UBound(vData, 1)
If Not .exists(vData(lRow, 1)) Then
vArray(lRow, 0) = "x"
.Add vData(lRow, 1), Nothing
End If
Next lRow
End With
Application.ScreenUpdating = False
With ActiveSheet
.Range("BB3").Resize(UBound(vArray, 1)) = vArray
On Error Resume Next
.Range("BA34274", .Cells(Rows.Count, "BA").End(xlUp)).Offset(, 1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
.Columns(52).Resize(, 2).ClearContents ' throwing an error
End With
Application.ScreenUpdating = True
End Sub
他帮我,我们已经使用了Y和Z列这些目的,现在,我必须使用BA和BB列这些任务。我不知道在哪里做出改变。我取代Z,使用“BB”和y与“BA”列,但其引发错误应用程序定义的或在这些线
.Columns(52).Resize(, 2).ClearContents
其中我必须作出改变和顶部1和2行是对象定义用于标题。细胞从第三排开始。请帮助我使用这些代码。任何帮助是极大的赞赏
我已经改变
.FormulaR1C1 = "=RC[-24]&RC[-23]&RC[-19]&RC[-18]&RC[-17]&RC[-16]" these to
.FormulaR1C1 = "=RC[-52]&RC[-51]&RC[-47]&RC[-46]&RC[-45]&RC[-44]" these
我猜它一定是正确的
答
你几乎拥有它,它应该是:
.Columns(53).Resize(, 2).ClearContents
但我不看看它是如何产生错误的。
此外,如果你不喜欢的R1C1表示法你可以使用:
With ActiveSheet.Range("A3", Cells(Rows.Count, "A").End(xlUp)).Offset(, 52)
.Formula = "=A3&B3&F3&G3&H3&I3"
vData = .Resize(, 1).value
End With
你也应该离开的空间,它有助于可读性。
Sub Delete_Duplicate_Codes()
Dim vData As Variant, vArray As Variant
Dim lRow As Long
With ActiveSheet.Range("A3", Cells(Rows.Count, "A").End(xlUp)).Offset(, 52)
.Formula = "=A3&B3&F3&G3&H3&I3"
vData = .Resize(, 1).value
End With
ReDim vArray(1 To UBound(vData, 1), 0)
With CreateObject("Scripting.Dictionary")
For lRow = 1 To UBound(vData, 1)
If Not .exists(vData(lRow, 1)) Then
vArray(lRow, 0) = "x"
.Add vData(lRow, 1), Nothing
End If
Next lRow
End With
Application.ScreenUpdating = False
With ActiveSheet
.Range("BB3").Resize(UBound(vArray, 1)) = vArray
On Error Resume Next
.Range("BA34274", .Cells(Rows.Count, "BA").End(xlUp)).Offset(, 1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
.Columns(53).Resize(, 2).ClearContents
End With
Application.ScreenUpdating = True
End Sub
Niko,是代码工作还是你还需要帮助? – Reafidy
@Reafidy其实我没有测试代码呢!我正在与其他活动一起工作我将在星期一进行测试我没有时间测试 – niko
@Reafidy测试代码后,我会让你知道是否有任何改变,并感谢您的帮助 – niko