Sub-totaling and gross totaling

Hi experts,

I have a range of data that requires subtotaling and gross totaling. Trying to figure out the vba code that subtotals exactly on next row of the end of cluster and gross totals at the end.

I have attached the document for reference.

Thank you,
Roshan Shakya

Hi !

First just try the Excel feature SubTotal (may not need any code) …

I need that one for the automation. Sub-totaling and gross totaling are activities that I use frequently in my report. And these tasks neither come at the beginning nor at the end.

The result is exactly what I want.

But considering the volume of data and the number of worksheets, subtotaling manually is time consuming. Also data range is dynamic as there are number of additions and disposal every period. So it would be great if I could write some code that will capture subtotals at different levels and at the end grand total.

The code you suggested is a nice piece of code but due to my ignorance I am not able to figure out how to sum/sub total exactly the same way.

I could get the sum total exactly on the same cell by using SUMIF formula (on some other columns), condition (derived by using IF(ISNUMBER(SEARCH(""Total :"",RC[-13])),0,1)) and Loop to this condition (if 0, then).

Hi jefferrybrown,

Can you please suggest me the code for subtotalling at different levels? I am unable to modify in the code that you have suggested as I am still a novice in coding.

Thank you for your time.

See if this is how you wanted.

Please Login or Register  to view this content.

Sorry, but don't understand what you are trying to say.

I just coded according to your attachment.

Why it didn't work is beyond my imagination. The code is phenomenal and I must say you are a genius. Can you please explain how it works? The code seems overwhelming to me.

I have made slight changes in the worksheet and you can definitely help me to learn to accommodate some variations. If I could pick/set identifiers for each level, I may total the group figures for whatever levels ain't I?

Thank you jindon for such a quick response with such an efficient code.

Originally Posted by Roshan.Shakya

...slight changes...

No,,,it's a huge change.

See if this places the formula properly.

Please Login or Register  to view this content.

Again?

What is your purpose changing the data layouts all the time?

The layouts are generally dynamic since there are different but unique worksheets for a variety of circumstances. I misunderstood that it will be just a tweaking of codes for (additional blank) rows or columns and shifting of columns.

The pattern however in both layouts is the same except there is a shifting of column E in first layout, and removal of blank rows in second layout.

The layouts have similar patterns with extra rows after every level in the first layout (and without in the last layout). The patterns have been presented in the document enclosed.

Thank you for your response.

So what do you want to do?

You should have uploaded a workbook with more worksheets with different data layouts.

You are now asking almost different question.

Sorry for delayed response jindon.

I have those 2 sheets at the moment where I perform sub-totaling. I try to fathom the code that you have suggested and noted that "x" and "r" do the sub-totaling and grand-totaling respectively. Since we have 3 layers of subtotaling can we do something like this?

X does the first level of sub totaling (sum of all items in X)
Y does the second level, and (sum of all Xs)
R does the grand totaling (sum of all Ys)

If there are more than 1 R then there will be a grand total of all Rs.

I am sharing the screenshots for my explanation. The clue is that rows 1661 and 1662 have subtotals, which indicate first level in 1661 and second level in 1662. Similarly, rows 2377, 2378 and 2379 have subtotals that indicate first, second and third level of subtotals. So if there are consecutive sub-totals, the code should consider moving to the higher levels of sub totaling but ignoring the previous sub totals that is already used in that higher level.

Thank you
Roshan

Hi Jindon and respected gurus,

Can you please help me to get this solved? The file that I have attached earlier is the exact representation of the data I am handling. I have no requirement of subtotaling in other files at the moment.

Thank you
Roshan Shakya

Hi respected gurus and experts,

I am still waiting for a solution on this? Shall I close this thread?

Thank you
Roshan Shakya

Respected experts, gurus and MVPs,

I have come up with simple subtotal solution on varying levels of totals but for this matter, I have renamed the Total to Total: (in 2nd Level) and Grand Total: (in 3rd Level). I am attaching the solution herewith with the code below.

Please Login or Register  to view this content.

In my report, my problem is I cannot rename Total in 2nd level and 3rd level to Total: and Grand Total: respectively. Can I tweak something in the code so that I do not have to work manually on source data? The original data has all value pasted figures. I would want to close this post and would request for the solution.

Thank you
Roshan Shakya

Thank you all gurus and experts for your contribution and sharing your valuable knowledge. I am closing this thread as solved.

Roshan Shakya