Excel Crazy

Merging Multiple Rows based on Criteria -Excel VBA


Most of the time we deal with duplicate values in excel, same situation we have here. In our below sales dataset, we have Product Category, Products, and sales amount info in column A,B and C.

What we are looking for is the total of sales amount in “Column C” based on multiple records by product category. Also capture all the products as comma separated in one cell in column B. For reference check below screenshot


Sub mergemultiplelines()

    Dim lastrow As Long
    Dim i As Integer
    lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    With ActiveSheet
        'Sort data by Product Category before merging
        .Cells(1).CurrentRegion.Sort key1:=.Cells(1), Header:=xlYes
Backward loop to delete merged rows
            For i = lastrow To 2 Step -1
                'Product category conditional check
                If .Cells(i, 1) = .Cells(i - 1, 1) Then
                    .Cells(i - 1, 2) = .Cells(i - 1, 2) & ", " & .Cells(i, 2)
                    .Cells(i - 1, 3) = .Cells(i - 1, 3) + .Cells(i, 3)
                End If
            Next i
    End With


When using VBA to scan a text for number, the basic approach is like this:
1. Read each character in a given text.
2. See if it is number using ASCII values, If so, extract it.
3. Hold the number in variable.
4. Continue with next character.


1. Go to developer Tab else Press Alt + F11
2.Click on Visual Basic icon
3.Go to Insert click on Module

User defined function in excel

4.Paste below code in standard module

December 27, 2017

0 responses on "Merging Multiple Rows based on Criteria -Excel VBA"

Leave a Message

Your email address will not be published. Required fields are marked *


© LOKESH PALIWAL 1987 - 2018


Reporting Solutions | Data Consulting | Analytics Training

close slider

Your Name (required)

Your Email (required)

Your Phone no. (required)

Your Message for us...

Skip to toolbar