How to Remove Duplicates From Array Using VBA in Excel?
Last Updated :
09 Nov, 2021
Excel VBA code to remove duplicates from a given range of cells. In the below data set we have given a list of 15 numbers in “Column A” range A1:A15. Need to remove duplicates and place unique numbers in column B.
Sample Data: Cells A1:A15
Sample Data
Final Output:
VBA Code to remove duplicates and place into next column (B)
Declare Variables:
Variables | Data Type | Comments |
---|
nonDuplicate | Boolean | It is a Boolean value (True/False). |
uNo | Integer | Count no of Unique items in column B |
colA | Integer | Iteration column A cells |
colB | Integer | Iteration column B cells |
'Variable Declarations
Dim nonDuplicate As Boolean, uNo As Integer, colA As Integer, colB As Integer
Always first value will be unique, So A1 place to cell B1
'Place first value to B1
Cells(1, 2).Value = Cells(1, 1).Value
Initialize variables:
'Initialize uNo = 1 since first number is already placed in column B; Assign True to the variable nonDuplicate
uNo = 1
nonDuplicate= True
Since the first number is already placed in cell B1, Loop starts from A2 to A15. Take each number from Column A and check with Column B (unique range)
'Use for loop to check each number from A2 to A15
For colA = 2 To 15
For colB = 1 To uNo
if the number is already placed in column B. Assign False to the "nonDuplicate" variable.
If Cells(colA, 1).Value = Cells(colB, 2).Value Then
nonDuplicate= False
End If
"nonDuplicate" is True then place to column B and increase uNo by 1
'if nonDuplicate is true, place cell value in column B and increase uNo = uNo + 1
If nonDuplicate = True Then
Cells(uNo + 1, 2).Value = Cells(colA, 1).Value
uNo = uNo + 1
End If
Reset "nonDuplicate" variable
'reset nonDuplicate to True
nonDuplicate = True
Close for loop
Next colA
Implementation:
Follow the below steps to remove duplicates using Excel VBA:
Step 1: Add a shape (VBA Remove Duplicates) to your worksheet
Step 2: Right-click on “VBA Remove Duplicates” and “Assign Macro..”
Step 3: Select “removeDuplicates”, you can see a list of macros available in your workbook
Step 4: Save your excel file as “Excel Macro-Enabled Workbook” *.xlsm
Step 5: Click “VBA Remove Duplicates” to execute VBA code and see the output
Similar Reads
How to remove duplicate values from array using PHP? In this article, we will discuss removing duplicate elements from an array in PHP. We can get the unique elements by using array_unique() function. This function will remove the duplicate values from the array.Syntax:array array_unique($array, $sort_flags);Note: The keys of the array are preserved i
4 min read
How to Remove Duplicate Elements from an Array using Lodash ? Removing duplicate elements from an array is necessary for data integrity and efficient processing. The approaches implemented and explained below will use the Lodash to remove duplicate elements from an array. Table of Content Using uniq methodUsing groupBy and map methodsUsing xor functionUsing un
3 min read
How to Find and Remove Duplicates in Excel Inacurate data always ruin your data and that the reason removing duplicates in Excel because it is one of the reason for duplicate data in Excel. In this guide, we aer going to explain you the best ways to find and remove duplicates in Excel. How to Find and Remove Duplicates in ExcelIn the section
9 min read
How to Remove Duplicate Elements from Array in Ruby? This article focuses on discussing how to remove duplicate elements from an array in Ruby. In Ruby, an array is a fundamental data structure that stores a collection of elements in a specific order. Various methods and approaches can be used to remove duplicate elements from an array. Removing Dupli
2 min read
How to Remove Duplicate Elements from NumPy Array In this article, we will see how to remove duplicate elements from NumPy Array. Here we will learn how to Remove Duplicate Elements from a 1-D NumPy Array and 2-D NumPy Array.Input1: [1 2 3 4 5 1 2 3 1 2 9] Output1: [1 2 3 4 5 9] Explanation: In this example, we have removed duplicate elements from
7 min read
How to Remove duplicate elements from array in JavaScript ? In this article, we will discuss the methods to remove duplicate elements from a Javascript array. There are various methods to remove duplicates in the array. These are the following ways: Table of Content Using filter() MethodUsing set() MethodUsing forEach() MethodUsing reduce() MethodUsing index
4 min read
How to Find Duplicate Values in Excel Using VLOOKUP? Excel is a great tool for working with data. One of its handy features is the VLOOKUP function, which helps you find matching or duplicate values in your data. In this article, weâll show you how to use VLOOKUP to spot duplicates in a simple way. Youâll learn how to compare two columns in one sheet,
3 min read
How to Delete a Module in Excel VBA? Have you ever seen a text file in a notepad or a source code file in visual studio code? These all are just basic files where you can write some code or information. Similarly, we have modules in excel, each module has its code window, where you can write some code and allow it to automate your repe
3 min read
Remove Duplicate Elements from TypeScript Array TypeScript array is a single variable that is used to store the elements or a group of values of the same type. An array can contain multiple occurrences or duplicates of an element that can be removed using the below-discussed methods.Below are the approaches used to remove Duplicate Elements from
4 min read
How to Use for Each Loop in Excel VBA? A For Each loop is used to execute a statement or a set of statements for each element in an array or collection. Syntax: For Each element In group [ statements ] [ Exit For ] [ statements ] Next [ element ] The For...Each...Next statement syntax has the following three parts: PartDescriptionelement
3 min read