Looking for faster alternative to looping for determining SUM of array elements

[mchac]

I have a sheet with a data range that's about 50,000x100. I load that into an array for testing and one of the tests is to see if a set of cells are all zero. Right now I'm doing this by looping through each array element (roughly from array(c,20) to array(c,100)) to see if the sum is zero. Depending on the answer the sub branches off.

        ACCTG_ENTRY_TOTAL = 0
        For c = 20 To COLUMN_OF_INTEREST_NUMBER - 1  'Column #20 is the first with Financial data
            ACCTG_ENTRY_TOTAL = ACCTG_ENTRY_TOTAL + DATA_ARRAY1(a, c)
        Next c

This is currently the slowest part of the routine by far.

Can anyone suggest a faster way to determine if a set of elements in an array are all zero rather than looping.

 

[Rick Rothstein]

I have a sheet with a data range that's about 50,000x100. I load that into an array for testing and one of the tests is to see if a set of cells are all zero. Right now I'm doing this by looping through each array element (roughly from array(c,20) to array(c,100)) to see if the sum is zero. Depending on the answer the sub branches off.

        ACCTG_ENTRY_TOTAL = 0
        For c = 20 To COLUMN_OF_INTEREST_NUMBER - 1  'Column #20 is the first with Financial data
            ACCTG_ENTRY_TOTAL = ACCTG_ENTRY_TOTAL + DATA_ARRAY1(a, c)
        Next c

This is currently the slowest part of the routine by far.

Can anyone suggest a faster way to determine if a set of elements in an array are all zero rather than looping.

What about this...

IsAllZeroes = (WorksheetFunction.Max(DATA_ARRAY1) = 0)

 

[mchac]

Thanks Rick.
I don't want to delete the negative elements but I guess the test would be if Min = Max = 0.
It's a 2d array and if all elements from roughly 'col' 20 to 100 are blank (or zero) then I can delete the row.
Does your idea work for something like that?

 

[mchac]

Fazza, I took your idea to have the sub create a dynamic Sum formula, paste in a column, sort on that column, search for the range where the zeros start and end, then apply range.entirerow.delete.
Previously it took ~827 secs to run everyting. Now it takes ~63. So the part just examining for zeros shows an even greater improvement because the other parts of the code are unchanged.

Thanks!

 

[Rick Rothstein]

Thanks Rick.
I don't want to delete the negative elements but I guess the test would be if Min = Max = 0.
It's a 2d array and if all elements from roughly 'col' 20 to 100 are blank (or zero) then I can delete the row.
Does your idea work for something like that?

Maybe this will work for you then...

IsAllZeroes = (WorksheetFunction.Max(DATA_ARRAY1) = WorksheetFunction.Min(DATA_ARRAY1))

 

[mirabeau]

using only one worksheet function

YourArray = Range("A1").Resize(50000, 100)
s = WorksheetFunction.SumSq(YourArray) = 0
MsgBox s

However, generating a test array of size 50,000 by 100 with all zeros by the testdata code

Range("A1").Resize(50000, 100) = 0

and doing some timing testing, it seems to me that a looping code like

Dim c, b As Boolean
YourArray = Range("A1").Resize(50000, 100)
For Each c In YourArray
    If Not c Then b = True: Exit For
Next c
MsgBox b

is the fastest by quite some margin. It also works (for me at least) on some massive arrays where the worksheet function approaches fail.