Error handling when dynamic name range is empty

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

Hi Leith,

Thanks for your prompt response. Sorry to say this but when both DB & Test ranges are empty, I get a run-time error 1004: application defined or object defined error with the following code highlighted in yellow

With Names("Test").RefersToRange

Any ideas why this is happening?

Many thanks
Asha

Hi all,

I have taken another stab at this and it seems to work but I am not sure if this is a very robust & efficient as I ended up writing additional code for three separate processes depending on the scenarios!

Any suggestions in cleaning up the code would be greatly appreciated.

Thanks again
Asha

Option Base 1
Option Explicit

Public Sub ConcatenateArrayLeith1()
    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 1st list (single column range) to 1D array
    On Error Resume Next
    With Names("Test").RefersToRange
        vArray1 = WorksheetFunction.Transpose(.Parent.Evaluate("IF(ROW(" & .Address & ")," & .Address & ")"))
    End With
    
    'Transpose the 2nd list (single column range) to 1D array
    On Error Resume Next
    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)
    
    'Checks if both the lists have NOT been populated
    If IsEmpty(vArray1) And IsEmpty(vArray2) Then
        MsgBox "Company list in both data entry form and database are empty. Nothing to update"
    'Checks if either of the lists have been populated
    ElseIf IsEmpty(vArray1) And Not IsEmpty(vArray2) Then
        GoTo Process1
    ElseIf Not IsEmpty(vArray1) And IsEmpty(vArray2) Then
        GoTo Process2
    'Checks if both the lists have been populated
    ElseIf Not IsEmpty(vArray1) And Not IsEmpty(vArray2) Then
        GoTo Process3
    End If
    Exit Sub
    
Process1:
    MsgBox "Company list in data entry form is empty. Nothing to update"
    vArray3 = vArray2
    ReDim Preserve vArray3(1 To iSizeA2)
    For i = 1 To iSizeA2
        vArray3(i) = vArray2(i)
    Next i
    Exit Sub
    
Process2:
    vArray3 = vArray1
    ReDim Preserve vArray3(1 To iSizeA1)
    For i = 1 To iSizeA1
        vArray3(i) = vArray1(i)
    Next i
    Exit Sub
        
Process3:
    '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
    Exit Sub
   
End Sub