Forum Discussion

RandomPanda's avatar
RandomPanda
Brass Contributor
Jul 12, 2021
Solved

Need VBA to auto-sum two fields

I'm fairly confident in my ability to edit VBA, but starting from scratch is a little more challenging.  I have a file with production numbers, sorted by product code. The nature of my business is t...
excel
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jul 12, 2021

    RandomPanda 

    Assuming that the data are in columns A to D and have headers in row 1:

    Sub Combine()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Header:=xlYes
    For r = m - 1 To 2 Step -1
        If Range("A" & r + 1).Value = Range("A" & r).Value Then
            Range("C" & r).Value = Range("C" & r).Value + Range("C" & r + 1).Value
            Range("D" & r).Value = Range("D" & r).Value + Range("D" & r + 1).Value
            Range("A" & r + 1).EntireRow.Delete
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

    If you don't have headers in row 1, use

        For r = m - 1 To 1 Step -1
German_Chris's avatar
German_Chris
Iron Contributor
Jul 12, 2021
Why do you not use a Pivottable or normal Grouping or PowerQuery to Group and get a new Table.
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jul 12, 2021

German_Chris You are sooooo right. Quite amazing to see that people still choose to revert to complicated VBA solutions to do very simple things, rather than using built-in functionalities that require a few button clicks. No coding required. Attaching a workbook that demonstrates just that. 

Resources