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.

 

[CheryBTL]

Try this:
Application.CountIf(Sheets(1).Range("A:E"), 0)

The data range Sheets(1).Range("A:E") changes based on you data.

 

[mchac]

Thanks for your reply. Can I use that with an array?

 

[shg]

No, COUNTIF, among other functions, only works with the first argument as a range.

 

[mchac]

ok, thanks.

 

[shg]

You're welcome.

 

[Fazza]

I'd a thought it would be not too slow as is. I guess all variables are explicitly dimmed.

The check is if all entries are zero however you're adding them up. Did you consider just checking if every one is zero? Saves adding them & you could exit the loop if a non-zero entry is found - so wouldn't always need to loop from 20 to the end.

 

[mchac]

It's the piece that I wait the longest to complete. Out of about a 5 -6 min process about 4+ mins.
Good point about just checking the value and exiting if <>0. That will definitely save processing time. I'll test that.

I also had the thought today that I should change the order I do things in the sub and process this part before putting the data into an array. That way I can try CheryBTL's idea. Or try .RemoveDuplicates which should be faster still. That would leave me with only one row with zeros to eliminate.

 

[Fazza]

What about sorting the worksheet range before starting? Is that an option? (Maybe use a SUM formula to help) identify the rows with zeros, and exclude them from the data loaded to the array.

 

[mchac]

The process needs to be automated so I guess I could have the sub input a Sum formula over a dynamic range, sort on that Sum, delete the Sum col then load into the array. Lemme think about that.
Thanks Fazza.