Create macro to delete rows if sum=0 for each ID#

Question

I need help creating a macro that can delete rows of data when the sum of the amounts in Column B equals zero for each identification number in Column A. I've used pivot tables and subtotals in the past, but with thousands of rows, it is still a time-suck.

For example:

 1 1001    6 
 2 1001   -1
 3 1002   15
 4 1002  -13
 5 1001   -5

I would want it to delete the rows with ID# 1001 because they zero out, but leave the rows with ID# 1002 completely alone.

Thanks! I appreciate all the help I can get.

Welcome to Stack Overflow. SO is not a code for me site. If you have code that does not work please post it in the original post and tell us what it is doing that is in error and we will help you overcome the specific problem with the code. — Scott Craner, Jun 23 '16 at 17:09
This may be a good starting point: http://stackoverflow.com/questions/915317/does-vba-have-dictionary-structure — Weasemunk, Jun 23 '16 at 17:12

score 0 · Answer 1 · answered Jun 23 '16 at 17:19

I think the easiest way to do this is by adding in an extra "helper" column. For example's sake, suppose your data was in, say, range A1:B1000, you could put the following formula into cell C1:

C1: =SUMIF($A$1:$A$1000,A1,$B$1:$B$1000)

And drag it down for all 1000 rows. Then simply filter for 0 in column C and delete those rows. At the end, of course you could also delete column C and viola.

Hope that helps!

Create macro to delete rows if sum=0 for each ID#

1 Answers1