VBA Removing duplicates in an array [duplicate]

Question

I have a string that looks like "apples//apples//oranges//" I'm getting an error "subscript out of range" when I try to remove duplicates.

I want my end result to look like "apples//oranges//"

Dim duplicateArray() As String
Dim programsArray() As String

duplicateArray() = Split(Sheets("Sheet1").Cells(1, 12).Value, "//")

For j = 0 To UBound(duplicateArray)
    If UBound(Filter(programsArray, duplicateArray(j))) > -1 Then

    Else
    programsArray(UBound(programsArray()) + 1) = duplicateArray(j)

    End If

Next j

    programElement = Join(programsArray, " // ")
    Sheets("Sheet1").Cells(1, 3).Value = programElement

sorry, it should be Sheets("Sheet1").Cells(1, 3).Value = programElement — user3757739
– user3757739, Commented Jun 19, 2014 at 19:03
I want programsArray to by a dynamic array. How do you define it so that I can add new text to it? — user3757739
– user3757739, Commented Jun 19, 2014 at 19:04

Horaciux · Accepted Answer · 2014-06-19 19:05:24Z

1

To dynamicaly resize an array:

ReDim [ Preserve ] name(boundlist)

Use Preserve to keept previous data stored in array

Dim duplicateArray() As String
Dim programsArray() As String

duplicateArray() = Split(Sheets("Sheet1").Cells(1, 12).Value, "//")

For j = 0 To UBound(duplicateArray)
    If UBound(Filter(programsArray, duplicateArray(j))) > -1 Then

    Else
    redim preserve programsArray(UBound(programsArray()) + 2)
    programsArray(UBound(programsArray()) + 1) = duplicateArray(j)

    End If

Next j

    programElement = Join(programsArray, " // ")
    Sheets("Sheet1").Cells(1, 3).Value = programElement

answered Jun 19, 2014 at 19:05

Horaciux

6,5152 gold badges26 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3757739 Over a year ago

I am still getting the error "subscript out of range" It seems to be from "If UBound(Filter(programsArray, duplicateArray(j))) > -1 Then"

Gary's Student · Accepted Answer · 2014-06-19 19:11:31Z

0

Consider:

Sub DeDup()
    duplicateArray = Split(Sheets("Sheet1").Cells(1, 12).Value, "//")
    Dim c As Collection
    Set c = New Collection
    On Error Resume Next

    For Each d In duplicateArray
        c.Add d, CStr(d)
    Next d

    programsArray = c(1)
    For i = 2 To c.Count
        programsArray = programsArray & "//" & c(i)
    Next i

    Sheets("Sheet1").Cells(1, 3).Value = programsArray
End Sub

answered Jun 19, 2014 at 19:11

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Collectives™ on Stack Overflow

VBA Removing duplicates in an array [duplicate]

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related