Arrays to replace very slow loops ?

[Guest]

Hi I had a post here asking if my loops that run very slowly can be made any
quick.
One suggestion was to replace with an Array.

Now I'm new to VBA and have no idea how to go about Arrays. Below is my code
where I'm finding dulicates based on name fields deleting from that and
adding to another sheet. Here I have 2 text boxes to enter position of Names
Fields and and an option to choose entire FName to be searched instead of
just FName initial.

Now all that I did is replace my r and k range with arrays but I get and
error at this line : If Trim(UCase(Array1(i).Cells(n, strFNameCol))) =
Trim(UCase(Array1(i).Cells(m, strFNameCol))) And _
Trim(UCase(Array1(i).Cells(n, strLNameCol))) = Trim(UCase(Array1(i).Cells(m,
strLNameCol))) . It says runtime error "Runtime error 9 Subscript out of
Range"

My code as below:

Private Sub CmdSubmitNames_Click()
Dim r As Range, _
k As Range
Dim sh As Excel.Worksheet
Dim strFNameCol As String, _
strLNameCol As String
Dim intCounter As Integer, _
intTotDB As Integer, _
totRows As Integer, _
intDupFound As Integer, _
intTotDB2 As Integer, _
i As Integer, _
intTotfile As Integer

Dim Array1(), _
Array2()

totRows = 1026
intCounter = 0

strFNameCol = TxtFNCol.Value
strLNameCol = TxtLNCol.Value
Set r = ActiveWorkbook.ActiveSheet.Range("A:AS")
Set sh = ActiveWorkbook.Worksheets.Add
Set k = sh.Range("A:AS")

Array1 = r.Value
Array2 = k.Value
intTotDB = 1
n = 2

For n = 2 To 1000
If (Array1(i).Cells(n, strFNameCol)) <> "" Or _
(Array1(i).Cells(n, strLNameCol)) <> "" Then

For m = n + 1 To 1000
If OptEntireFNSearch Then
If Trim(UCase(Array1(i).Cells(n, strFNameCol))) =
Trim(UCase(Array1( i).Cells(m, strFNameCol))) And _
Trim(UCase(Array1(i).Cells(n, strLNameCol))) =
Trim(UCase(Array1(i).Cells(m, strLNameCol))) Then
intDupFound = 1
Array2(i).Rows(intTotDB).Value = Array1(i).Rows(m).Value
intTotDB = intTotDB + 1
Array1(i).Rows(m).Delete
m = m - 1
totRows = totRows - 1
End If
Else
If Trim(UCase(Left(Array1(i).Cells(n, strFNameCol), 1))) =
Trim(UCase(Left(Array1(i).Cells(m, strFNameCol), 1))) And _
Trim(UCase(Array1(i).Cells(n, strLNameCol))) =
Trim(UCase(Array1(i).Cells(m, strLNameCol))) Then
intDupFound = 1
Array2(i).Rows(intTotDB).Value = Array1(i).Rows(m).Value
intTotDB = intTotDB + 1
Array1(i).Rows(m).Delete
m = m - 1
totRows = totRows - 1
End If
End If
Next m


If intDupFound = 1 Then
Array2(i).Rows(intTotDB).Value = Array1(i).Rows(n).Value
intTotDB = intTotDB + 1
Array1(i).Rows(n).Delete
totRows = totRows - 1
n = n - 1
intDupFound = 0
End If

End If
Next n

End_of_Data:
MsgBox "Data Extracted"

End Sub

Thanks In Advance

 

[Tom Ogilvy]

An array assiged as you have will be a 2 dimensional array.

Since you are picking up values. the array elements will not have properties
like row and so forth.

Since you delete a row when you find a match, you can then throw out your
array since it will no longer match your worksheet.

 

[Alan Beban]

An array assiged as you have will be a 2 dimensional array.

Since you are picking up values. the array elements will not have properties
like row and so forth.

Since you delete a row when you find a match, you can then throw out your
array since it will no longer match your worksheet.

It may well be that the OP is not yet up for working with VBA arrays,
notwithstanding the dramatic speed of execution advantage they can
provide. If he/she did want to mount the subject matter, here are a few
comments.

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.

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...)

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. 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

 

[Tom Ogilvy]

Comment in line.

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.

 

[Alan Beban]

Comment in line.




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"

Granted; it would have to be converted to a number.

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.

Granted; depends on the OP's intention for Trim.

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.

I thought it was fairly clear that my comment assumed that the code was
rewritten to delete the row from the array within the loop,
notwithstanding that that would not be by built-in functions. That was
the point of my comment "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." Although
"reconstructing the whole array" may sound daunting, it can often be
accomplished by a fairly straightforward loop in memory.

Of course the changed array will no longer match



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.

If it would, then it should certainly be considered. I would suspect
that since the identification of the targeted rows would continue to be
accomplished by looping through ranges rather than looping through
arrays, and only the individual deletions of each row would be replaced
by the en masse deletion, similar speed advantages would not in fact be
achieved; but maybe someone will be up for measuring that approach. Or
the OP might simply implement it and see whether he/she achieves some
improvement.

I think elaboration of the general concept of looping through arrays
instead of ranges is worthwhile because looping through memory is so
often dramatically faster than looping through worksheet ranges; thanks
for your comments.

Alan Beban

 

[Tom Ogilvy]

If it would, then it should certainly be considered. I would suspect
that since the identification of the targeted rows would continue to be
accomplished by looping through ranges rather than looping through
arrays, and only the individual deletions of each row would be replaced
by the en masse deletion, similar speed advantages would not in fact be
achieved; but maybe someone will be up for measuring that approach. Or
the OP might simply implement it and see whether he/she achieves some
improvement.

I posted code in the original thread that would do it and no looping was
required. It certainly isn't a new technique and is well proven in terms of
speed.


Here it is again:

assume this can be determined by looking at the values in column A

Sub DeleteDups()
Dim rng As Range
Columns(2).Insert
Set rng = Range(Cells(1, 1), _
Cells(Rows.Count, 1).End(xlUp))
rng.Offset(0, 1).Formula = _
"=if(countif($A$1:A1,A1)>1,na(),false)"
rng.Offset(0, 1).SpecialCells(xlFormulas, _
xlErrors).EntireRow.Delete
Columns(2).Delete
End Sub