How to sum values for similar text in a different column via VBA macro

[Excel_Learner29]

Hi Team,

I am looking some help on creating a macro which could help me sum values in column for B for similar text in col A and insert the sum field in col b for after every group its sums up and also insert a blank row after its sum the data

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: left"]Names[/TD]
[TD="class: xl65, width: 64, align: left"]Value[/TD]
[/TR]
[TR]
[TD="class: xl65, align: left"]Apple[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD="class: xl65, align: left"]Apple[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl65, align: left"]Orange[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl65, align: left"]Orange[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD="class: xl65, align: left"]Milk[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="class: xl65, align: left"]Milk[/TD]
[TD="align: right"]40[/TD]
[/TR]
</tbody>[/TABLE]



Macro to return :-

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl67, width: 64"]Names[/TD]
[TD="class: xl67, width: 64"]Value[/TD]
[/TR]
[TR]
[TD="class: xl65, align: left"]Apple[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD="class: xl65, align: left"]Apple[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl66, align: right"]35[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: left"]Orange[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl65, align: left"]Orange[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl66, align: right"]55[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: left"]Milk[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="class: xl65, align: left"]Milk[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl66, align: right"]60[/TD]
[/TR]
</tbody>[/TABLE]
Appreciate your help

 

[Joe4]

Welcome to the Board!

Take a look at using Subtotals or Pivot Tables. Both of these should be able to do that for you without any need of VBA.
If you do Google Search, you will find some good tutorials on how to use these two Excel features.

 

[Excel_Learner29]

Hi ,
thank you for replying..but it would be a great help if the same can be achieved by a macro since this is going to be a re-occurring thing for me to do at office and also will be need to send in a format like this only and with involves a huge data...so will be little difficult to make pivot because i need data to be represented like above and not club them and give sum of one single value like pivot.

Would appreciate your help please.

 

[Joe4]

Did you take a look at what Subtotals produces?
It insert rows at changes like that automatically.
You can create the Subtotals from VBA with one simple line, i.e.

Sub InsertSubtotals()
    ActiveSheet.UsedRange.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

 

[Excel_Learner29]

Thanks so much! that would also work!!

 

[Joe4]

You are welcome!

Note that is just using the Subtotals method I proposed, but then just having VBA do it instead of you doing it manually.
A lot of times you can automate manual actions by turning on the Macro Recorder, and record yourself doing the steps manually (which will give you the VBA code to perform those functions).

 

[Excel_Learner29]

ok,l thanks , i will try doing that step too...muchappreciated!!