Excel VBA – Combine rows with duplicate values in one cell and merge values in other cell

by

Try changing your code to this:

Sub mergeCategoryValues()
    Dim lngRow As Long

    With ActiveSheet
        lngRow = .Cells(65536, 1).End(xlUp).Row
        .Cells(1).CurrentRegion.Sort key1:=.Cells(1), Header:=xlYes

        Do
            If .Cells(lngRow, 1) = .Cells(lngRow - 1, 1) Then
                .Cells(lngRow - 1, 3) = .Cells(lngRow - 1, 3) & "; " & .Cells(lngRow, 3)
                .Cells(lngRow - 1, 4) = .Cells(lngRow - 1, 4) + .Cells(lngRow, 4)
                .Rows(lngRow).Delete
            End If

            lngRow = lngRow - 1
        Loop Until lngRow = 1
    End With
End Sub

Tested

enter image description here

EDIT

To make it a little easier to adjust to different column I added variables at the beginning to indicate which column do what. Note that column 2 (B) isn’t used in the current logic.

Sub mergeCategoryValues()
    Dim lngRow As Long

    With ActiveSheet
        Dim columnToMatch As Integer: columnToMatch = 1
        Dim columnToConcatenate As Integer: columnToConcatenate = 3
        Dim columnToSum As Integer: columnToSum = 4

        lngRow = .Cells(65536, columnToMatch).End(xlUp).Row
        .Cells(columnToMatch).CurrentRegion.Sort key1:=.Cells(columnToMatch), Header:=xlYes

        Do
            If .Cells(lngRow, columnToMatch) = .Cells(lngRow - 1, columnToMatch) Then
                .Cells(lngRow - 1, columnToConcatenate) = .Cells(lngRow - 1, columnToConcatenate) & "; " & .Cells(lngRow, columnToConcatenate)
                .Cells(lngRow - 1, columnToSum) = .Cells(lngRow - 1, columnToSum) + .Cells(lngRow, columnToSum)
                .Rows(lngRow).Delete
            End If

            lngRow = lngRow - 1
        Loop Until lngRow = 1
    End With
End Sub

More Related Contents:

  1. Duplicate number and it value in column EXCEL [closed]
  2. using for loop for vba
  3. Simple VBA code to open cells one after the other
  4. Open folder in new instance
  5. VBA Macro for copying and pasting columns on same excel sheet
  6. Copy data from cell in selected row
  7. Refer to sheet using codename
  8. VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds
  9. Assign code to a button created dynamically
  10. Last not empty cell (column) in the given row; Excel VBA
  11. HTML Text with tags to formatted text in an Excel cell
  12. Split comma separated entries to new rows [closed]
  13. Get list of Excel files in a folder using VBA [duplicate]
  14. Select first empty cell in column F starting from row 1. (without using offset )
  15. What does ReDim Preserve do?
  16. VBA – How the colon `:` works in VBA code with condition
  17. How to create a separate CSV file from VBA?
  18. VBA: How to change the value of another cell via a function?
  19. Running VBA from a HYPERLINK()
  20. Sending formatted Lotus Notes rich text email from Excel VBA
  21. How can I generate GUIDs in Excel?
  22. Range.Find not making a difference between January and November (February and December) in VBA Excel
  23. Remove Duplicates from range of cells in excel vba
  24. Excel Select vs Activate
  25. Fast compare method of 2 columns [closed]
  26. How can an Excel Add-In respond to events in any worksheet?
  27. Fastest way to check if two ranges are equal in excel vba [duplicate]
  28. How do I get a list of unique values from a range in Excel VBA?
  29. How to get selected value in multicolumn listbox
  30. Query my excel worksheet with VBA

Leave a Comment