Excel VBA: Create two-dimensional array from multiple range variables (no duplicates)

Question

I have six variable ranges: A_backup, B_backup, C_backup, D_backup, E_backup, F_backup

Each range variable is one column with a variable number of rows (some have 3, others have 5, etc.)

I would like to take each cell from these ranges and add them to a new single column array called Combined_backups. I would also like to avoid adding a cell if it is a duplicate string value to a previously added cell.

Here's what I've tried. Running into issues with the Combined_backups.RemoveDuplicates. Should I make a new range for the combined array, apply the RemoveDuplicates, then create a new final array? Also, what's the best way to test that my Combined_backups array has actually become the array I was hoping for?

Dim Combined_backups() As Variant

'add A_backup
Dim j As Integer
j = A_backup.Rows.Count

ReDim Preserve Combined_backups(j)

For i = 0 To j - 1
    Combined_backups(i) = A_backup.Item(i + 1)
Next i

'add B_backup
Dim k As Integer
k = B_backup.Rows.Count

ReDim Preserve Combined_backups(j + k)

For i = 0 To k - 1
    Combined_backups(i) = B_backup.Item(i + 1)
Next i

'add C_backup
Dim l As Integer
l = C_backup.Rows.Count

ReDim Preserve Combined_backups(j + k + l)

For i = 0 To l - 1
    Combined_backups(i) = C_backup.Item(i + 1)
Next i

'add D_backup
Dim m As Integer
m = D_backup.Rows.Count

ReDim Preserve Combined_backups(j + k + l + m)

For i = 0 To m - 1
    Combined_backups(i) = D_backup.Item(i + 1)
Next i

'add E_backup
Dim n As Integer
n = E_backup.Rows.Count

ReDim Preserve Combined_backups(j + k + l + m + n)

For i = 0 To n - 1
    Combined_backups(i) = E_backup.Item(i + 1)
Next i

'add F_backup
Dim o As Integer
o = F_backup.Rows.Count

ReDim Preserve Combined_backups(j + k + l + m + n + o)

For i = 0 To o - 1
    Combined_backups(i) = F_backup.Item(i + 1)
Next i

'elminate duplicates from Combined_backups
Combined_backups.RemoveDuplicates

Thanks!

Answer 1

Here's a different approach using the Collection object. We first put everything into a Collection, using to our advantage its property of rejecting duplicates; then we put the collection object into a "results" array, and write it back to a worksheet. This assumes your various arrays are named ranges and not range objects, but you should be able to adapt as needed:

Option Explicit

Sub UniqueArray()
    Dim vSrc As Variant
    Dim colStrings As Collection
    Dim vVarRanges As Variant
    Dim vResults() As Variant
    Dim S As String
    Dim I As Long, J As Long, K As Long

vVarRanges = VBA.Array("A_backup", "B_backup", "C_backup", "D_backup", "E_backup", "F_backup")

Set colStrings = New Collection
On Error Resume Next 'So collection will omit any duplicates instead of causing an error
For I = 0 To UBound(vVarRanges)
    vSrc = Range(vVarRanges(I))
    For J = 1 To UBound(vSrc, 1)
        S = vSrc(J, 1)
        If Len(S) > 0 Then _
            colStrings.Add Item:=S, Key:=CStr(S)
    Next J
Next I
On Error GoTo 0

'Now create results array
ReDim vResults(1 To colStrings.Count, 1 To 1)
For I = 1 To colStrings.Count
    vResults(I, 1) = colStrings(I)
Next I

'Write the results someplace

With Worksheets("sheet4").Range("A1").Resize(rowsize:=UBound(vResults))
    .EntireColumn.Clear
    .Value = vResults
End With

End Sub