Solved: Using VBA to Sum changing groups of Rows

[pcarmour]

Hi,
I have a spreadsheet that has rows of data in groups thatchange when new rows are added to any of the groups.
I have a programme that loads a new row of data, sorts thegroups and inserts a blank row below each group.
I now want to SUM each group individually on row ‘E’.
I have tried with some (activecell , .End(xlUp)) efforts buthave failed to get it to work.
Sorry I don’t have any good coding examples.
I am using Windows 7 and Excel 14.0.4734.1000 (32 bit)
Regards,
Peter

[p45cal]

I have a programme that loads a new row of data, sorts thegroups and inserts a blank row below each group. Would this be an Excel VBA macro? If so show the code and we can probably tweak.



I now want to SUM each group individually on row ‘E’.Is the data you want to sum in column E, or is that where you want the result - or both?
A good idea of how your data is actually arranges on the sheet would be helpful - perhaps an example sheet shared on the interweb somewhere - though sharing just the code here might be enough as suggested above.

[pcarmour]

Hi, Yes it's a VBA Macro.

Here is my coding but it does need a little tidying up, and there is no good sum section relative to my question. Thanks for your help.
Sub CommandButton1_Click()
Dim Sell_Order_Stock_Code As String
Dim Sell_Order_Stock_Name As String
Dim Sell_Order_ISIN As String
Dim Sell_Order_GTC As OptionButton
Dim Sell_Order_Recd_Time As Date
Dim Sell_Order_Amount As Integer
Dim Sell_Order_Limit As Integer
Dim Sell_Order_Instructions As String
Dim Sell_Order_Broker As ComboBox

Dim LR As Long 'for group spacing
Dim i As Long
Dim Area As Range

Application.ScreenUpdating = False

If Sell_Order.Stock_Code.Value <> "" Then

Range("D3").Select
ActiveCell.Value = Sell_Order.Stock_Code 'stock Code
Range("K3").Select
ActiveCell.Value = Sell_Order.Stock_Name 'Stock Name
Range("L3").Select
ActiveCell.Value = Sell_Order.ISIN 'ISIN
Range("M3").Select
ActiveCell.Value = "" 'Custody

Else

With Sell_Order.Holdings
Range("D3").Value = .List(.ListIndex, 0) 'stock Code
'Range("??").Value = .List(.ListIndex, 1) 'Amount - use to check for over selling
Range("K3").Value = .List(.ListIndex, 2) 'Stock Name
Range("L3").Value = .List(.ListIndex, 3) 'ISIN
Range("M3").Value = .List(.ListIndex, 4) 'Custody
End With
End If




Range("A3").Select
ActiveCell.Value = "=Today()"
Range("B3").Select
If Sell_Order.Recd_Time.Value = "" Then
ActiveCell.Value = "=NOW()"
Selection.NumberFormat = "hh:mm"
Else
ActiveCell.Value = Format(Sell_Order.Recd_Time.Value, "hh:mm")
End If
Range("A5:B3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Range("C3").Select
ActiveCell.Value = "S" 'B/S
Range("E3").Select
ActiveCell.Value = Format(Sell_Order.Amount.Value, "#,###") ' Amount
Range("F3").Select
ActiveCell.Value = Format(Sell_Order.Limit.Value, "#,###.000000") 'Limit
Range("G3").Select
If GTC.Value = True Then
ActiveCell.Value = "GTC"
Else
ActiveCell.Value = "" 'Day order
End If
Range("H3").Select
ActiveCell.Value = Format(Sell_Order.Instructions.Value, "") 'Instructions

Range("J3").Select
ActiveCell.Value = Format(Sell_Order.Broker.Value, "") 'Broker


Sell_Order.Hide


Range("A5:M5").Select
Selection.Copy
Range("A3:M3").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Rows("3:100").Select
Rows("3:100").EntireRow.AutoFit

Range("A2:M100").Select
ActiveWorkbook.Worksheets("Absolute").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Absolute").Sort.SortFields.Add Key:=Range("D3:D100" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Absolute").Sort.SortFields.Add Key:=Range("C3:C100" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Absolute").Sort.SortFields.Add Key:=Range("I3:I100" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Absolute").Sort.SortFields.Add Key:=Range("G3:G100" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Absolute").Sort.SortFields.Add Key:=Range("B3:B100" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Absolute").Sort
.SetRange Range("A2:M100")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Rows("3:3").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

'Range("P65536").End(xlUp).Select
' enter spaces to groups
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 5 Step -1
If Range("D" & i).Value <> Range("D" & i - 1).Value Then
Rows(i).EntireRow.Insert

End If
If Range("B" & i).Value = "" Then Range("E" & i).Value = ""
Next i
Range("E" & Range("E" & Rows.Count).End(xlUp).Offset(1).Row).Value = ""


'ActiveCell.Formula = "=SUM(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-5, 0).End(xlUp))"

Range("A3").Select
End Sub

[p45cal]

Try doing it while inserting the rows by changing:LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 5 Step -1
If Range("D" & i).Value <> Range("D" & i - 1).Value Then
Rows(i).EntireRow.Insert

End If
If Range("B" & i).Value = "" Then Range("E" & i).Value = ""
Next i
to:LR = Range("A" & Rows.Count).End(xlUp).Row
LastInsertedRowWasAt = LR + 1
For i = LR To 5 Step -1
If Range("D" & i).Value <> Range("D" & i - 1).Value Then
Range("E" & LastInsertedRowWasAt).FormulaR1C1 = "=SUM(R[" & i - LastInsertedRowWasAt & "]C:R[-1]C)"
LastInsertedRowWasAt = i
Rows(i).EntireRow.Insert
End If
If Range("B" & i).Value = "" Then Range("E" & i).Value = ""
Next i

[pcarmour]

Tahnk you but can you please confirm what definitions I should use?

[pcarmour]

Hi, Your code looks exactly what I'm looking for but I'm not sure how to define 'last inserted row was at'. which it now halts.

Am I missing a way of acknowledging you?
Regards,
Peter.

[pcarmour]

Hi,
Thank you very much, I defined the code and now IT WORKS A TREAT!
Regards,
Peter

[p45cal]

Hi, Your code looks exactly what I'm looking for but I'm not sure how to define 'last inserted row was at'. which it now halts.Dim LastInsertedRowWasAt as Long



Am I missing a way of acknowledging you?Nope, a reply is all that was needed.

[kumaran P]

Region tag Value Chennai A 5000 Chennai A 5100 Chennai B 5400 Chennai B 5700 tamilnadu A 6000 tamilnadu A 6300 tamilnadu B 6600 kerala A 7100 kerala A 7400 kerala A 7900 kerala B 8200




i want given summary for above data

Region A B Value andra 14900 16900 31800 Chennai 10100 11100 21200 Delhi 18300
18300 kerala 22400 8200 30600

[p45cal]

pivot table
9345