Code works, but really slow. How to make it faster?

[pupsia]

Hello all,

I was able to write a code that I need and works, but one part of the code works really slow when there is a lot of data.

I need to work with huge lists sometimes, and when I tested the code with about 500 lines, I had to wait quite a lot of time.

Could someone please help me make the code faster? The "Loop" part is the one that takes up a lot of time. But as I`m still quite new to macro, not sure how to change that...

Sub Get_Unique_Senders()


    Dim r As Range, i As Long, ar
    Dim c As Integer
    


Application.ScreenUpdating = False




ThisWorkbook.Worksheets("Report").Range("C2", ThisWorkbook.Worksheets("Report").Range("C2").End(xlDown)).Copy Destination:=ThisWorkbook.Worksheets("Temp").Range("A1")


Sheets("Temp").Select




Set wb = ThisWorkbook.Worksheets("Temp")


    With wb
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With




For c = 2 To LastRow
    With Range("A" & c)
        .Value = WorksheetFunction.Trim(.Value)
    End With
    
    With Range("A" & c)
        If Right(.Value, 1) = ";" Then .Value = Left(.Value, Len(.Value) - 1)
    End With


wb.Range("A2:A" & LastRow).Select


Selection.Replace What:="; ", Replacement:=";", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False


Next




    Set r = wb.Range("A" & LastRow)
    Do While r.Row > 1
        ar = Split(r.Value, ";")
        If UBound(ar) >= 0 Then r.Value = ar(0)
        For i = UBound(ar) To 1 Step -1
            r.Copy
            r.Offset(1).Insert
            r.Offset(1).Value = ar(i)
        Next
        Set r = r.Offset(-1)
    Loop




With wb
    .Range("A1", .Range("A1").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlYes
End With


ThisWorkbook.Worksheets("Temp").Range("A1", ThisWorkbook.Worksheets("Temp").Range("A12").End(xlDown)).Copy Destination:=ThisWorkbook.Worksheets("Sheet7").Range("A1")


ThisWorkbook.Worksheets("Sheet7").Columns(1).AutoFit


Sheets("Sheet7").Select


Application.ScreenUpdating = True


End Sub

 

[Peter_SSs]

Any idea why that could be?

I don't trust 'Remove Duplicates'. To see why, do the small exercise suggested in post 4 here.

 

[pupsia]

I don't trust 'Remove Duplicates'. To see why, do the small exercise suggested in post 4 here.

Wow...That is quite new I always thought that the 'Remove Duplicates' works just fine and did not expect something like this. In a way, worried about the data I used to work and had to use the 'Remove Duplicates'. Hoe everything was good there..

Thank you for the additional information!! This is really good to know