Grouping data using VBA in Microsoft Excel

In this article, we will create a macro to group data on behalf of certain conditions.

Raw data consists of Business Development team data. It consists of Name, Phone Number and Sales per call.

ArrowRawData

In this article, we want to group data by agent name and the total revenue generated by each agent.

ArrowOutput

Logic explanation

We have created “DataGrouping” macro, which performs the required calculation and grouping the data. It gives a unique agent name and the total revenue generated by each agent.

Code explanation

LngLastRow = Cells(Rows.Count, 1).End(xlUp).Row

The above code is used to get the row number of the last cell.

While Not Cells(i, 1).Value = ""

Wend

The above code is used to loop until a blank cell is encountered.

For LngRow = LngLastRow To (i + 1) Step -1

Next LngRow

The above For loop is used for reverse looping, starting from the last row to specify first row.

Rng.Offset(0, 2).Value = Rng.Offset(0, 2).Value + Cells(LngRow, 3).Value

The above code is used to sum up the values based on the specified criteria.

Rows(LngRow).Delete

The above code is used to delete the row.

Please follow below for the code

Option Explicit

Sub DataGrouping()

'Declaring variables
Dim Rng As Range
Dim LngRow As Long, LngLastRow, i As Long

Application.ScreenUpdating = False

'Getting row number of last cell
LngLastRow = Cells(Rows.Count, 1).End(xlUp).Row

'Initializing the first row
i = 12

'Looping until blank cell is encountered in first column
While Not Cells(i, 1).Value = ""
    
    'Initializing range object
    Set Rng = Cells(i, 1)
    
    'Looping from last row to specified first row
    For LngRow = LngLastRow To (i + 1) Step -1
        
        'Checking whether value in the cell is equal to specified cell
        If Cells(LngRow, 1).Value = Rng.Value Then
            Rng.Offset(0, 2).Value = Rng.Offset(0, 2).Value + Cells(LngRow, 3).Value
            Rows(LngRow).Delete
        End If
    Next LngRow
    
    i = i + 1
    
Wend

Application.ScreenUpdating = True

End Sub

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com

Cameron
August 26, 2013 at 10:18 am

I have a macro that returns an answer(s) for a particular set of inputs. There are many sets of inputs that I have setup in rows. Can you provide an example of a loop structure that will proceed through each set of inputs (rows) and for each loop, insert the resulting answer(s) into another column on that row. The range of rows and corresponding columns containing the values to be inserted into the macro can be initially selected or hard coded in.

Reply ↓