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

#### mchac

##### Well-known Member
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.

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### CheryBTL

##### New Member
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
No, COUNTIF, among other functions, only works with the first argument as a range.

ok, thanks.

You're welcome.

#### Fazza

##### MrExcel MVP

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,084
Messages
5,857,269
Members
431,867
Latest member
Dalorian

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

### Which adblocker are you using?

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

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