vba code to remove duplicates from a column

Answered on: Wednesday 15 May, 2024 / Duration: 15 min read

Programming Language: VBA , Popularity :5/10

Solution 1:

To remove duplicates from a column in VBA, you can use the following code:

vba
Sub RemoveDuplicates()
 Dim ws As Worksheet
 Dim lastRow As Long Set ws = ThisWorkbook.Sheets("Sheet1") 'Change "Sheet1" to the name of your worksheet
 lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'Assuming column A has the data
 ws.Range("A1:A" & lastRow).RemoveDuplicates Columns:=1, Header:=xlNo
End Sub

In this code:
1. We define a variable ws to represent the worksheet where the data is located.
2. We determine the last row with data in column A.
3. We use the RemoveDuplicates method to remove duplicates from column A without considering the header row.

When you run this code, it will remove duplicates from column A in the specified worksheet.

For example, if your column A data is:

After running the code, the output will be:

Make sure to adjust the code to fit your specific worksheet and column needs.

Solution 2:

Remove Duplicates from a Column in VBA

Code Example:

vba
Sub RemoveDuplicates()
 ' Get the active worksheet.
 Dim ws As Worksheet
 Set ws = ActiveSheet
 ' Get the range of data in the column.
 Dim dataRange As Range
 Set dataRange = ws.Range("A1:A100")
 ' Create a new range to store the unique values.
 Dim uniqueRange As Range
 Set uniqueRange = ws.Range("B1:B100")
 ' Remove duplicates from the data range.
 dataRange.RemoveDuplicates Columns:=1, Header:=xlYes
 ' Copy the unique values to the new range.
 uniqueRange.Value = dataRange.Value
End Sub

Output:

The code creates a new column with the unique values from the original column.

Explanation:

* The Set statement assigns objects to variables.
* The Range object represents a range of cells on the worksheet.
* The RemoveDuplicates method removes duplicate values from a range.
* The Columns argument specifies which columns to remove duplicates from.
* The Header argument specifies whether the range has a header row.
* The Value property gets or sets the values in the range.

Example Walkthrough:

The code below demonstrates how to remove duplicates from column A and place the unique values in column B:

vba
Sub RemoveDuplicates()
 Dim ws As Worksheet
 Set ws = ActiveSheet
 Dim dataRange As Range
 Set dataRange = ws.Range("A1:A10")
 Dim uniqueRange As Range
 Set uniqueRange = ws.Range("B1:B10")
 dataRange.RemoveDuplicates Columns:=1, Header:=xlYes
 uniqueRange.Value = dataRange.Value
End Sub

Before:

| Column A | Column B |
|---|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |

After:

| Column A | Column B |
|---|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |

Solution 3:

Sure, I'd be happy to help you with that! To remove duplicates from a column in VBA, you can use the Range.RemoveDuplicates method. Here's an example of how you can use this method to remove duplicates from Column A in a worksheet named "Sheet1":

vba
Sub RemoveDuplicates()
 ' Define the worksheet
 Dim ws As Worksheet
 Set ws = ThisWorkbook.Sheets("Sheet1")
 ' Remove duplicates from Column A
 ws.Columns("A").RemoveDuplicates Columns:=Array(1), Header:=xlYes
End Sub

In the code above, we first define the worksheet that we want to work with by setting the ws variable to the "Sheet1" worksheet.

Next, we use the Columns property of the ws object to target Column A. We then call the RemoveDuplicates method on this range and pass it two arguments:

1. Columns:=Array(1) - This argument tells VBA to look for duplicates in Column 1 (i.e., Column A). You can adjust the array value to target different columns. For example, if you want to remove duplicates from Columns A and B, you would use Columns:=Array(1, 2).
2. Header:=xlYes - This argument tells VBA whether the range contains a header row. If your range does contain a header row, you should set this argument to xlYes. If your range does not contain a header row, you should set this argument to xlNo.

Once you run the code, you should see that all duplicate values in Column A have been removed, leaving only the unique values.

### Output

Assuming you started with the following data in Column A:

| A |
| --- |
| Apple |
| Banana |
| Apple |
| Cherry |
| Banana |
| Cherry |
| Cherry |