VBA - Popular array string and do not repeat values

Question

I have an Excel Workbook x. This Workbook has 3 spreadsheets. The 1st is Animals still empty; the 2nd is Birds and we have repeated values and the 3rd is Mammals also with repeated values.

How to popular a string array with Birds and Mammals values without repeating values and throw array values into Animals?

Here is the link of the contents of the spreadsheet in Google Drive.

https://docs.google.com/spreadsheets/d/19oQGI2mUrR8lIrjhGXwfqHjB75zm3GXBquokeSxdhkE/edit?usp=sharing

I appreciate all your help.

Answer 1

Problem

With the data of Birds in the spreadsheet "Birds"

+----------+
| Eagle    |
| Penguin  |
| Heron    |
| Flamingo |
| Toucan   |
| Penguin  |
| Heron    |
| Eagle    |
| Flamingo |
| Flamingo |
| Heron    |
| Heron    |
| Toucan   |
| Eagle    |
| Penguin  |
| Eagle    |
| Eagle    |
| Heron    |
| Flamingo |
| Penguin  |
| Heron    |
| Flamingo |
| Toucan   |
| Penguin  |
| Heron    |
| Eagle    |
| Flamingo |
| Flamingo |
| Heron    |
| Heron    |
| Toucan   |
| Eagle    |
| Penguin  |
| Eagle    |
| Eagle    |
| Heron    |
| Flamingo |
+----------+

And the data of Mammals in the Spreadsheet "Mammals"

+---------+
| Bear    |
| Zebra   |
| Gnu     |
| Giraffe |
| Pig     |
| Dog     |
| Cow     |
| Zebra   |
| Cow     |
| Pig     |
| Bear    |
| Giraffe |
| Cow     |
| Gnu     |
| Gnu     |
| Bear    |
| Dog     |
| Dog     |
| Dog     |
| Zebra   |
| Gnu     |
| Giraffe |
| Pig     |
| Dog     |
| Cow     |
| Zebra   |
| Cow     |
| Pig     |
| Bear    |
| Giraffe |
| Cow     |
| Gnu     |
| Gnu     |
| Bear    |
| Dog     |
| Dog     |
| Dog     |
+---------+

Code

'Declara Variáveis
Dim BirdsArray() As Variant, MammalsArray() As Variant, AnimalsArray() As Variant
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim UltimaLinha1 As Long, UltimaLinha2 As Long, i As Long, j As Long

'Define as planilhas
Set ws1 = ThisWorkbook.Worksheets("Birds")
Set ws2 = ThisWorkbook.Worksheets("Mammals")
Set ws3 = ThisWorkbook.Worksheets("Animals")

'Última linha da coluna A de cada Planilha
UltimaLinha1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
UltimaLinha2 = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row

'Preenche os vetores com os dados das células da coluna A de cada planilha
BirdsArray = ws1.Range("A1:A" & UltimaLinha1).Value
MammalsArray = ws2.Range("A1:A" & UltimaLinha2).Value

'Checar Valores da Array
'For i = LBound(BirdsArray) To UBound(BirdsArray)
'    Debug.Print BirdsArray(i, 1)
'Next i
'
'For j = LBound(MammalsArray) To UBound(MammalsArray)
'    Debug.Print MammalsArray(j, 1)
'Next j

'Redimensiona o vetor dos animais para o tamanho de dados da soma dos outros dois vetores
ReDim AnimalsArray(UBound(BirdsArray) + UBound(MammalsArray) + 1, 1)

'Preenche o novo Vetor com os dados dos outros vetores
For i = LBound(BirdsArray) To UBound(BirdsArray)
    AnimalsArray(i, 1) = BirdsArray(i, 1)
Next i
j = 1
For i = UBound(BirdsArray) + 1 To (UBound(BirdsArray) + UBound(MammalsArray))
    AnimalsArray(i, 1) = MammalsArray(j, 1)
    j = j + 1
Next i

'Checa os valores do novo vetor
'For j = LBound(AnimalsArray) To UBound(AnimalsArray)
'    Debug.Print AnimalsArray(j, 1)
'Next j

'Código do SOen de valores únicos na coleção
'https://stackoverflow.com/questions/3017852/vba-get-unique-values-from-array
Dim arr As New Collection, a
On Error Resume Next
For Each a In AnimalsArray
    arr.Add a, a
Next

For i = 1 To arr.Count
    ws3.Cells(i, 1) = arr(i)
Next

Upshot

+----------+
| Eagle    |
| Penguin  |
| Heron    |
| Flamingo |
| Toucan   |
| Bear     |
| Zebra    |
| Gnu      |
| Giraffe  |
| Pig      |
| Dog      |
| Cow      |
+----------+