macro for for totaling columns in dynamic/varying ranges

Copper Contributor

I would like to record a macro for totaling about 10 of 15 columns in a range. Some rows have cells that are formatted as currency with two decimal places, some with 24-hour time, and some with numbers to 1 decimal place (i.e., mileage). But I am trying to total my weekly tutoring activity, which consists of a number of rows that varies by the week. I tried recording the macro and running it on a range with a different number of rows, but of course it totaled the same number of rows as the original range for which I recorded, and did not adjust to the different number of rows in the new range. I did try recording using relative references to see if that would make it automatically adjust the selected cells when running the macro. But it did not.  

 

I have more than 1 week of data in the same worksheet, so totaling the whole worksheet would not work for getting just one week of totals.

 

I always insert 2 rows of blank cells between ranges of weekly activity; I would think having blank rows should help to separate the ranges when automating the SUM function with a macro.

 

Could someone please tell me how to set up a macro so that I can run it on ranges of cells that vary in the number of rows from week to week? I don't know much about VBA code so it would be easiest for me to do it all by properly setting up the macro with little to no editing in VBA.

 

Thank you.

8 Replies

@MBleau Don't think you need a macro for this. Are you familiar with the use of Pivot Tables? Make sure all your data entries are in one table/range. Thus, no empty rows between weeks. Let Excel do the summaries for you based on the dates. Can you upload an example of your data? Make sure you delete and private and/or confidential information.

@Riny_van_Eekelen Thanks, Riny. I have a beginner's familiarity with pivot tables. I could set these up. I could do without blank rows and potentially shade each consecutive week in a different color to distinguish individual weeks. But in using pivot tables, I would be able to place them to the right of the columns containing the source data (instead of below all the ranges of the weeks), correct? That would be best visually. In my Totals rows, I need to be able to edit text for notes in cells in my "Notes" column, e.g., for more detailed invoicing information, and not only include number totals in these rows. Would that work in a pivot table? Please see the attached screenshot. The row in green is my Totals row, totaled manually. So I would not have this row if using a pivot table as you are suggesting.

 

MS Screen Shot 2021-03-19.png

 

@MBleau Not sure why you would care to put a pivot table next to the underlying data or why you would want to shade weeks in the You can if you want to but for what purpose? It's not really clear to me, from your screenshot, what you are trying to achieve. Sorry.

Well, ignore the shading for now. Where would the pivot tables show up, then for each week? I don't want them each in separate worksheets as that would create a great number of worksheets. I currently have 1 sheet per month and a few others. Thank you.

@MBleau I would need a file to see what you are doing. Can you upload one without confidential information?

@Riny_van_Eekelen. I am reluctant to upload files, but I can try what you advised so far, either trying to create the pivot table beside the source data, or in new worksheets. I hesitate to do in new worksheets as it will make the workbook more unwieldy; instead of ~ 15 worksheets, I would end up with over 50 because I need to total the tutoring activity for each week. Thanks for your help in recommending pivot tables.

@MBleau As said, it's difficult to recommend what to do without really understanding your intentions. But in general, you should keep all your raw data in one sheet. That is, not one for every month, no shading, no empty rows, no special formats. And then you would build your pivot tables (by week, on that single table/range containing the raw data. But perhaps this is not feasible in your particular situation.

Ok, @Riny_van_Eekelen. Thanks for your time and help :)