Need some changes to the below code

Question

This was the code suggested by Reafidy a programmer on stackoverflow. it was working as intended. Need a better optimized code? Now I have to Re-use the same code for a large file.

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

He helped me and we have used y and z columns for these purpose Now I have to use BA and BB columns for these task. I dont understand where to make the changes. I replaced z with "BB" and y with "BA" columns but its throwing an error application-defined or object-defined at these line

     .Columns(52).Resize(, 2).ClearContents

where I have to make changes and the top 1 and 2 rows are used for headers. The cells starts from 3rd row. Please help me with these code. any help is greatly appreciated

I have changed

 .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

I guess it must be right

Niko, is the code working or do you still need help with this? — Reafidy, Oct 02 '11 at 00:15
@Reafidy actually I did not test the code yet ! i was working with other activity i will test it on monday i did not have time to test it — niko, Oct 02 '11 at 08:50
@Reafidy after testing the code i will let you know if any changes are to be made and thanks for your help — niko, Oct 02 '11 at 08:50

Reafidy · Accepted Answer · 2011-09-28T20:36:12.837

You almost had it, it should be:

.Columns(53).Resize(, 2).ClearContents

But I don't see how it could have thrown an error.

Also if you dont like the R1C1 notation you can just use:

  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

You should also leave the spaces, it helps with readability.

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

there are some issues with the above code I dont know why its getting hang up I think becuase im using 220 MB file that may be the issue. Im changing my algorithm I need to use variants instead of accessiong sheet I will try to code it if i find any issues i will post it on stackoverflow and Thanks for your suport! — niko, Oct 04 '11 at 07:12
hi Niko, you could try posting at ozgrid.com/forum and send me a pm there. that way you can post your workbook and I can take a look. — Reafidy, Oct 04 '11 at 09:28
Sorry was reading your post on mobile, didn't see the file size of 220mb. That is very very big. I would say abnormally big, what does it contain? You should take a look at: http://www.ozgrid.com/Excel/ExcelProblems.htm — Reafidy, Oct 04 '11 at 19:30
Reafidy The issue is solved I used variants and it worked and amazingly fast but still Thanks for your code ! — niko, Oct 05 '11 at 06:28

Need some changes to the below code

1 Answers1

Linked