Adding subtotal in VBA

[Dee]

Hi all,

i have big list of 100 rows and 20 columns data for which i need to add sub total for each change in levels.

For Ex: Level 1 is broad category which have many Level 2's and Level 5's

Each level 2 is again have sub categories like 3's and 4's.

under each Level 2 we may or may not get level 3 or level 4. So the sum of Level 2 should be sub total of Level 3 & level 4 .Hope i am clear in my requirement.

Let me know if you need clarification...Your help in this is very much appreciated


Thanking you in advance

Dee


SL NO. LEVEL Total Cost

2 1 SUM(I4,I13,I15,I24)

3

4 2 SUM(I8,I11)

5 3 0.07

6 3 0.2

7 3 0.2

8 Subtotal of 3's SUM(I5:I7)

9 4 0.15

10 4 0

11 Subtotal of 4's SUM(I9:I10)

12

13 2 0

14

15 2 0

16 3 4

17 3 0.07

18 3 0.2

19 Subtotal of 3's SUM(I16:I18)

20 4 0.2

21 4 0.15

22 4 0

23 Subtotal of 4's SUM(I20:I22)

24 2 0

25 3 0

26 Subtotal of 3's 0

 

[Hui]

Have you tried the Sub_total command

goto Data, Sub-Total

follow prompts

 

[Dee]

Hi Hui,


Thanks for the reply...

Can't consider the subtotal of data toolbar as i am adding the rows and many formulas via macros...

 

[Luke M]

Dee,


You can put the Subtotal's in via macro. Here's an example bit of code:


Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(3), _

Replace:=True, PageBreaks:=False, SummaryBelowData:=True


check out the VB help file on Subtotal as it applies to a range for more detail.