Merging Multiple Rows based on Criteria -Excel VBA

Share Now


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


Share Now
November 15, 2019

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 *

    Tableau Training in Delhi

    All Rights Reserved. EC Analytics Consulting 2014 - 2019.

    NM 23, Sector 14, OLD DLF Colony - Gurgaon (Haryana) India. 9582876837 | Privacy Policy