Greetings,

The below macro creates a third list by concatenating two lists (Dynamic Ranges "Test" & "DB"). There could be three (which I can think of) situations that causes the code to error out.

1. Both Test & DB are empty - Nothing to be done
2. Test is empty but DB is not - Third list to be equal to DB
3. DB is empty but Test is not - Third list to be equal to Test

I tried to put in some error handling but it is not working as expected under different scenarios - I populated a range of cells with the values of the third list. Can someone please help me?

Much appreciated
Asha 

Option Explicit
Option Base 1

'This macro will create a third list by concatenating two lists
'---------------------------------------------------------------------------------------------------------------------
'Adapted: http://www.eggheadcafe.com/software/aspnet/33754898/how-to-combine-arrays.aspx (Joe)
'         http://www.ozgrid.com/forum/showthread.php?t=92426 (Post # 4)
'         http://www.excelforum.com/excel-programming/738644-type-mismatch-error-13-transpose-dynamic-name-range-when-range-refers-to-single-cell.html
'---------------------------------------------------------------------------------------------------------------------

Public Sub ConcatenateArray()
    Dim vArray1               'List of companies in the data entry form
    Dim vArray2               'List of companies in the database
    Dim vArray3               'Concatenated list
    Dim i As Integer
    Dim iSizeA1 As Integer      'Number of elements in vArray1
    Dim iSizeA2 As Integer      'Number of elements in vArray2

    'Transpose the lists (single column range) to a one dimensional array
On Error Resume Next 
    With Names("Test").RefersToRange
        vArray1 = WorksheetFunction.Transpose(.Parent.Evaluate("IF(ROW(" & .Address & ")," & .Address & ")"))
    End With
    MsgBox "Empty List. Nothing to update"

On Error Goto Errhandler
    With Names("DB").RefersToRange
        vArray2 = WorksheetFunction.Transpose(.Parent.Evaluate("IF(ROW(" & .Address & ")," & .Address & ")"))
    End With
        
    'Determine the number of elements in vArray1 & vArray2
    iSizeA1 = UBound(vArray1)
    iSizeA2 = UBound(vArray2)
           
    'Set vArray3 = vArray2 & then add elements from vArray1 to this list
    vArray3 = vArray2
    ReDim Preserve vArray3(1 To iSizeA1 + iSizeA2)
    For i = 1 To iSizeA1
        vArray3(iSizeA2 + i) = vArray1(i)
    Next i

Errhandler:
   vArray3 = vArray1
  
End Sub