I have an Excel WorkBook x. This WorkBook has 3 spreadsheets. The 1st is still empty; the 2nd is Birds and we have repeated values and the 3rd is Mammals also with repeated values.
How do you populate a string array with the values of Birds and Mammals without repeating values and throwing array values in Animals?
Here is the link to the spreadsheet content in Google Drive.
Thanks for all the help.
With the Birds data in the "Birds" Worksheet
+----------+
| 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 Mammals data on the Mammals Worksheet
+---------+
| 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 |
+---------+
'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 array
'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
+----------+
| Eagle |
| Penguin |
| Heron |
| Flamingo |
| Toucan |
| Bear |
| Zebra |
| Gnu |
| Giraffe |
| Pig |
| Dog |
| Cow |
+----------+