Large Data Set Summing Macro


    Hi, I am new to VBA and need help on a macro that can sum data ranges of different lengths.

    See attachment.

    For example, imagine data in cells A2:N25000 (obviously, this example in the attachment has been shortened). At this point, also imagine that rows 7 & 8 did not exist. So, there is a continuous stream of data for vaious counties in A2:N25000. For the purpose of context, the data contain mortgage information for all the lenders in a particular county for an entire US state.

    Is it possible to create a macro that would insert two rows after each county in the data range (e.g., rows 7 & 8 in the attachment)? In other words, in the first row a macro would insert a row (row 7) that sums the results of the top 25 lenders in each county. Then, the macro would insert a second row (row 8) that sums the results of ALL the lenders in each county?

    Assuming there aren't 25 lenders in a county, the macro would return the results for ALL the lenders in that county. In this instance, therefore, the results for the top 25 lenders in the county would be identical to the results for ALL the lenders in that county (so the two inserted rows inserted by the macro would contain the same information).

    In addition, if there are more than 25 lenders in a county (such as in Anchorage), the macro would insert only the sum of the top 25 lenders in the first row, followed by the sum of ALL the lenders in Anchorage in the second row.

    Again, I could do this manually (as I did for Aleutians East county in rows 7&8), but a macro would be most beneficial considering the total number of counties in the United States.

    I appreciate any insight you can provide.
    Thank you,

  • Re: Large Data Set Summing Macro


    I would not think you need a macro yet. The built in Excel functionality for subtotals may provide an even simpler answer. You may have to add a helper column to identify the amount you want summed for a top 25 lender.


  • Re: Large Data Set Summing Macro


    The subtotal feature did not provide a complete solution for me; however, your reply sparked me to start playing with pivot tables and I believe these will provide an answer.

    Thank you very much for your help. You were right--macros appear to have been unesseary in this instance.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!