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

mchac

Well-known Member
Joined
Apr 15, 2013
Messages
531
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.

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

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

CheryBTL

New Member
Joined
Dec 24, 2013
Messages
29
Try this:
Application.CountIf(Sheets(1).Range("A:E"), 0)

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

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
No, COUNTIF, among other functions, only works with the first argument as a range.
 

mchac

Well-known Member
Joined
Apr 15, 2013
Messages
531

ADVERTISEMENT

ok, thanks.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
You're welcome.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368

ADVERTISEMENT

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

Well-known Member
Joined
Apr 15, 2013
Messages
531
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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

Well-known Member
Joined
Apr 15, 2013
Messages
531
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,561
Messages
5,596,853
Members
414,107
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top