Summing up variable rows until specific value is reached, then delete remaining rows

NCLDL · Mar 9th 2018

Hi All,

I am trying to write an excel VBA loop that which would allow me to sum "count" (col B) in each group until i arrive at the target in (col C), where summing of count would stop at the last row summed which exceeds the target. After which any excess rows for each group will be deleted. To further clarify please see example below.

Example: For the first group "a", the VBA should stop summing at the 3rd cell (sum = 2845) as it is the last sum that exceeds the target of 2800 in col C. Then the entire other remaining 3 rows in group "a" (32, 35, 12) should be deleted.
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]

[/tr]

[tr]

[td]

Group

[/td]

[td]

Count

[/td]

[td]

Target

[/td]

[/tr]

[tr]

[td]

a

[/td]

[TD="align: right"]23[/TD]
[TD="align: right"]2800[/TD]

[/tr]

[tr]

[td]

a

[/td]

[TD="align: right"]465[/TD]

[td][/td]

[/tr]

[tr]

[td]

a

[/td]

[TD="align: right"]2357[/TD]

[td][/td]

[/tr]

[tr]

[td]

a

[/td]

[TD="align: right"]32[/TD]

[td][/td]

[/tr]

[tr]

[td]

a

[/td]

[TD="align: right"]35[/TD]

[td][/td]

[/tr]

[tr]

[td]

a

[/td]

[TD="align: right"]12[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td]

b

[/td]

[TD="align: right"]56[/TD]
[TD="align: right"]200[/TD]

[/tr]

[tr]

[td]

b

[/td]

[TD="align: right"]98[/TD]

[td][/td]

[/tr]

[tr]

[td]

b

[/td]

[TD="align: right"]34[/TD]

[td][/td]

[/tr]

[tr]

[td]

b

[/td]

[TD="align: right"]9[/TD]

[td][/td]

[/tr]

[tr]

[td]

b

[/td]

[TD="align: right"]54[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td]

c

[/td]

[TD="align: right"]670[/TD]
[TD="align: right"]300[/TD]

[/tr]

[tr]

[td]

c

[/td]

[TD="align: right"]2349[/TD]

[td][/td]

[/tr]

[tr]

[td]

c

[/td]

[TD="align: right"]87[/TD]

[td][/td]

[/tr]

[tr]

[td]

c

[/td]

[TD="align: right"]23[/TD]

[td][/td]

[/tr]

[/TABLE]

I have also attached the excel template if required. Please help!!!

Thank you.

Regard,
NC

chirayuw · Mar 12th 2018

try this

Code

Sub MyTarget()


Dim LstRow As Integer
Dim Target As Long
Dim Val As Long
Dim GRP As String


LstRow = Cells(Rows.Count, "A").End(xlUp).Row


Range("C2").Select
Do While ActiveCell.Row <= LstRow
   
    If Not IsEmpty(ActiveCell) Then Target = ActiveCell
    GRP = Range("A" & ActiveCell.Row)
   
    If GRP = "" And ActiveCell.Row <> LstRow Then
        ActiveCell.Offset(1, 0).Select
    Else
   
        Val = Range("B" & ActiveCell.Row)
        ActiveCell.Offset(1, 0).Select
       
        Do While GRP = Range("A" & ActiveCell.Row)
            If GRP = Range("A" & ActiveCell.Row) And Val <= Target Then
                Val = Val + Range("B" & ActiveCell.Row)
                ActiveCell.Offset(1, 0).Select
            Else
                ActiveCell.EntireRow.Delete
            End If
        Loop
       
        LstRow = Cells(Rows.Count, "A").End(xlUp).Row
   
    End If
   
Loop


End Sub

Display More

NCLDL · Mar 12th 2018

Thank you v much chirayuw!! This is just what i needed:)

Summing up variable rows until specific value is reached, then delete remaining rows

Participate now!

Share