Comment in line.

"Alan Beban" wrote in message
...
For starters, recognize that after
Dim r As Range
Set r = ActiveWorkbook.ActiveSheet.Range("A:AS")

in the syntax r.Cells(n, strFNameCol) the Cells method is redundant;
switch to r(n,strFNameCol)

Then after Set r = ActiveWorkbook.ActiveSheet.Range("A:AS")
Dim Array1 As Variant
Array1 = r



you can replace r(n,strFNameCol) with Array1(n,strFNameCol) to refer to
the same element in the array that matches the corresponding element in
the range. That is, with either the range or the array, n is the row
index and strFNameCol is the column index.

Since OP has dimmed strFNameCol as String and gotten it from a control, good
possibility it is a letter and would not work in the array. It would need
to be converted to a number as implied by you use of "column index"


Another correction to be made (although it doesn't relate to the
difference between looping through arrays and looping through ranges) is
that in your code Trim(whatever. . .) should be
Application.Trim(whatever...)

No reason to use Application.Trim if the intent is to remove spaces from the
front and back. The VBA trim works fine for this.


The third point of significance is that
r.Rows(m).Delete is not a syntax that works for arrays and a substitute
needs to be crafted to delete a row of an array.

Tom Ogilvy's point above about deleting a row when you find a match is a
bit beside the mark.

Appears the OP's intent is to delete a row as soon as a duplicate is
ound - not change the array. This would make the array used to make the
decision not match the new layout of the data on the worksheet. There is
no built in method for deleting a "row" in an array without reconstructing
the whole array. Not sure why this makes the point beside the mark.

Of course the changed array will no longer match
the worksheet; that's the point--you are not making the changes directly
to the worksheet; that's why the code executes faster. If the code is
rewritten to make the same changes to the array(s) that your prior code
intends to make to the range(s), you could then easily transfer the
arrays to the worksheet as ranges to replace the prior unchanged
worksheets. It might also be that it is more efficient to just keep
track of the rows to be deleted rather than deleting them in the arrays,
and then delete the targeted rows directly from the worksheet once the
targeting has been accomplished by looping through the arrays.

It is not a trivial exercise and may well be beyond what the OP cares to
deal with at present, despite the likely speed of execution improvement.
If so, well and good. If not, if the OP posts back with an email
address I will contact him to provide some additional guidance.

Alan Beban

It seems to me, using a couple of dummy columns to identify the rows to
delete and deleting them enmasse with Special cells would offer similar
speed advantages without the complexity you appear to suggest.

--
Regards,
Tom Ogilvy