Sub Remove_Duplicates()
Dim firstvalue As String
Dim lastvalue As String
Dim arraystr() As String
Dim x As Long
Dim k As Long
Dim cell As Range
Dim rw As Long
Application.ScreenUpdating = False
' for each cells in the range
For Each cell In Sheets("Sheet1").Range("A2:A19")
Erase arraystr ' erase array
lastvalue = "" ' erase final value"
firstvalue = cell.Value
On Error Resume Next
arraystr = Split(firstvalue, ",")
'Go into the full string and find duplication
For rw = 0 To UBound(arraystr)
For k = rw + 1 To UBound(arraystr)
If Trim(arraystr(k)) = Trim(arraystr(rw)) Then
arraystr(k) = "" 'if this is a duplication, delete the array value
End If
Next k
Next rw
' combine all the values without the duplicates
For x = 0 To UBound(arraystr)
If arraystr(x) <> "" Then
lastvalue = lastvalue & Trim(arraystr(x)) & ", "
End If
Next x
' Then delete the last empty space plus the extra comma
lastvalue = Trim(lastvalue)
lastvalue = Left(lastvalue, Len(lastvalue) - 1)
cell.Offset(0, 0).Value = lastvalue
Next cell
'AutoSize the column A an the end
Columns("A:A").Select
Selection.EntireColumn.AutoFit
Application.ScreenUpdating = True
End Sub
' for each cells in the range
For Each cell In Sheets("Sheet1").Range("A2:A19")
' for each cells in the range
For Each cell In Sheets("Sheet1").Range("F1:F200")
' for each cells in the range
For Each cell In Range("A1:A200")
If you want to run this macro on multiple columns, you need adjust the range as you like.
' for each cells in the range
For Each cell In Range("A1:G300")
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (3)
Commented:
2. You should parameterize your code so that it receives a range parameter rather than using hard coded ranges. Although it is not an in-place substitution, you can see an example of parameterized user-defined function in my Better Concatenation Function article.
http:A_7811.html
Alternatively, your code can prompt the user to select a range. In Excel, you have the ability to use the Application.Inputbox method with a data type of Range (8).
3. Like the range, you might allow the user to supply the delimiter.
4. If you had used a comma-space ", " delimiter rather than just the comma delimiter "," , you could eliminate the need to trim your split results. Any trimming actions could also be a parameterized/prompted.
Commented:
Example:
Open in new window
Produces the following results in the Immediate window:Open in new window
Commented:
If you used a dictionary object, you can do this:
Open in new window
Where rngCell refers to the current cell in your range iteration and dicUnique is a dictionary object variable with unique names.