Range.RemoveDuplicates could turn it all into a much more efficient one-liner.
Stylistically though, the code could use some help anyway.
The rather procedure begins with a good dozen variable declarations. Code is easier to follow when you don't need to constantly scroll up and down and back to know what's what: by declaring variables closer to their usage, you reduce the cognitive load and avoid that scrolling.
Dim xlApp As Application
Set xlApp = Excel.Application
Now, there's no need for this variable;
Application is free, globally-scoped, and you're not owning that instance - just copying a reference to an existing object. Don't do that, use the globals that are right there waiting to be referenced instead. This includes any worksheet that exist at compile-time in
Dim wsSource As Worksheet
Set wsSource = xlApp.ThisWorkbook.Worksheets("source")
Every worksheet has a
CodeName property, that you can change in the properties toolwindow (F4). Locate that "source" worksheet in the Project Explorer, then look at its
(Name) property - change it from what defaults to e.g.
Sheet1, to a meaningful identifier, e.g.
SourceSheet. And now you have a free, global-scope worksheet object referring to that specific sheet, readily usable everywhere in that VBA project - which makes this
wsSource variable redundant as well.
Dim rBefore As Range
Set rBefore = wsSource.Range(wsSource.Cells(2, 1), wsSource.Cells(wsSource.UsedRange.Rows.Count, 1))
It's not clear whether that
r stands for
Range (in which case it's useless Hungarian Notation), and
Before what this range is. So we try to look at other usages to infer its meaning... and we don't find any: the variable is assigned, but the assigned reference isn't used anywhere - the variable is redundant and can be removed entirely.
Dim ws As Worksheet
Set ws = xlApp.ThisWorkbook.Worksheets("testSheet")
CodeName could be
ws wouldn't be needed, provided that the worksheet exists at compile-time. Otherwise, kudos for fetching it from the
Dim r As Range
Set r = ws.Range(ws.Cells(2, 1), ws.Cells(ws.UsedRange.Rows.Count, 1))
UsedRange isn't a reliable way to get the last row of a column.
r isn't a meaningful name.
testRange would tell us it's from the
TestSheet, but wouldn't convey what it means - any single-letter variable name can never tell us anything whatsoever about something's purpose. Unless it's
i for a
For...Next loop counter, which any programmer knows about; here
i isn't used like that, it's more like some
DoEvents inside the loop, combined with updating the status bar at every single iteration, is contributing to slowing things down.
Calculation, etc.; but you leave
EnableEvents enabled, so a worksheet event is fired whenever you delete a row. You could use
Union to combine all the rows you want to delete, and then delete them all in one single operation - instead of deleting them one at a time. But then again,
Range.RemoveDuplicates makes that moot.
There are a number of issues (it's a constant work-in-progress), but Rubberduck could have picked up a number of the observations I made above. Kudos for
Option Explicit, the meaningful name, and the explicit
Public modifier on your macro!