How to Combine Duplicate Rows and Sum the Values in Excel VBA

When working with large amounts of data in Excel, you may come across situations where you have duplicate rows. Instead of manually going through and calculating the totals for these duplicate entries, you can use Excel VBA (Visual Basic for Applications) to automatically combine these rows and sum their values. This can be a handy tool for data cleaning and data preparation.


Step 1: Begin a New Macro

Start by creating a new macro. In Excel VBA, a macro is a piece of programming code that runs as a single task. It automates repetitive tasks, and you can design it according to your requirements.

Step 2: Define Variables and Set Conditions

The variables to define would generally be related to the data in the rows you want to combine. The cell reference (“A”) indicates the column from where the VBA will start searching for duplicates. Replace “A” with the column that best suits your needs.

Step 3: Sum the Value of the Rows and Delete the Duplicates

The final step is to sum up the values in your specified column and delete the row. The value “2” here corresponds to the column number that contains the values to be summed. Adjust it accordingly to suit your Excel sheet.

Here’s the Complete Code:



Excel VBA is a powerful tool that can help to streamline your data analysis process. By combining duplicate rows and summing their values, you can easily clean and structure your data. This tutorial demonstrated how to create a simple macro for this task. Remember to adjust the code to fit the structure of your specific worksheet.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Posted in vba