Solved: Combine Dynamic Range

[gnod]

Hi,

how can i combine dynamic range if the other dynamic range has no data?
pls refer to the sample file.. thanks..

[lucas]

can you explain what you mean by combine? Do you wish to sum or count or do you wish for your named range to be multi-column....???

[gnod]

i want to merge the data in Range1 up to Range3 but there are times that Range2 and Range3 has no data.. Range1 up to Range3 are dynamic range.. i need it to be dynamic but the vba code raise an error if the Range2 and Range3 has no data.. Thanks..

[mdmackillop]

Hi gnod
I've included Range 3 for testing; delete it if not required.
Regards
MD

Option Explicit
Option Base 1
Sub CombineNoDuplicate()
Dim CombArray() As Variant, stor1 As Variant
Dim Range1 As Range, Range2 As Range, Range3 As Range, OutputCell As Range, cell As Range
Dim n As Integer, i As Integer, j As Integer
Dim sorted As Boolean

On Error Resume Next
Set Range1 = Range("Range1")
Set Range2 = Range("Range2")
Set Range3 = Range("Range3")
Set OutputCell = Range("F2")

'Load the combined array with all values from both lists.
n = 0
If Not Range1 Is Nothing Then n = Range1.Cells.Count
If Not Range2 Is Nothing Then n = n + Range2.Cells.Count
If Not Range3 Is Nothing Then n = n + Range3.Cells.Count

ReDim CombArray(n, 2)
For Each cell In Range1
If Not Range1 Is Nothing Then
i = i + 1
CombArray(i, 1) = cell.Value
End If
Next cell
For Each cell In Range2
If Not Range2 Is Nothing Then
i = i + 1
CombArray(i, 1) = cell.Value
End If
Next cell
For Each cell In Range3
If Not Range3 Is Nothing Then
i = i + 1
CombArray(i, 1) = cell.Value
End If
Next cell

'Bubble Sort the combined array.
sorted = False
While Not sorted
sorted = True
For i = 1 To UBound(CombArray) - 1
If CombArray(i, 1) > CombArray(i + 1, 1) Then
stor1 = CombArray(i, 1)
CombArray(i, 1) = CombArray(i + 1, 1)
CombArray(i + 1, 1) = stor1
sorted = False
Exit For
End If
Next i
Wend

'Mark all duplicates in the array. True = Duplicate
CombArray(1, 2) = False
For i = 1 To UBound(CombArray) - 1
If CombArray(i, 1) = CombArray(i + 1, 1) Then
CombArray(i + 1, 2) = True
Else
CombArray(i + 1, 2) = False
End If
Next i

'Output results to range ignoring the duplicates.
j = 0
For i = 1 To UBound(CombArray)
If Not CombArray(i, 2) Then
OutputCell.Offset(j, 0).Value = CombArray(i, 1)
j = j + 1
End If
Next i
End Sub

[gnod]

Thanks.. it works.. :rotlaugh::clap: :thumb :) :bow:

i just delete the variable tmp coz it didn't use in the sub procedure.. thanks a lot.. :beerchug:

[mdmackillop]

BTW, You can use the Dictionary object to obtain a unique listing without the need for the second dimension in the array.

[gnod]

what do you mean? i don't understand.. :think:

[mdmackillop]

Alternative method

Option Explicit

Dim dic As New Collection

Sub CombineNoDuplicate()
Dim CombArray() As Variant, stor1 As Variant
Dim Range1 As Range, Range2 As Range, Range3 As Range, OutputCell As Range, Cell As Range
Dim i As Integer, j As Integer
Dim sorted As Boolean
Dim Swap1 As String, Swap2 As String

On Error Resume Next

Set Range1 = Range("Range1")
Set Range2 = Range("Range2")
Set Range3 = Range("Range3")
Set OutputCell = Range("F2")

If Not Range1 Is Nothing Then ItemList Range1
If Not Range2 Is Nothing Then ItemList Range2
If Not Range3 Is Nothing Then ItemList Range3

'Sort the collection
For i = 1 To dic.Count - 1
For j = i + 1 To dic.Count
If dic(i) > dic(j) Then
Swap1 = dic(i)
Swap2 = dic(j)
dic.Add Swap1, before:=j
dic.Add Swap2, before:=i
dic.Remove i + 1
dic.Remove j + 1
End If
Next j
Next i

'Output results to range
For i = 1 To dic.Count
OutputCell.Offset(i - 1, 0).Value = dic(i)
Next i
Set dic = Nothing
End Sub

'Write unique values to the collection
Sub ItemList(Rng As Range)
Dim Cell As Range
For Each Cell In Rng
On Error Resume Next
dic.Add Cell.Value, CStr(Cell.Value)
Next Cell
End Sub

[gnod]

Thanks.. i'll try this one.. may i ask, which one is the best..

[mdmackillop]

Personally I'd go for the second, which is basically simpler. The ItemList sub is the easiest way to get a list of unique items for adding to a list or combobox, which is another common usage.