vba - Can we merge two array using redim preserve

[Mallesh23]

Hi Team,

How to merge two array Data into Single Array using Redim Preserve and paste in Range("I1")

I have below data Range("A1").currentregion and Range("E1").currentregion
I want Output in Column I1.

aaa	aaa	aaa		bbb	bbb	bbb		aaa	aaa	aaa
aaa	aaa	aaa		bbb	bbb	bbb		aaa	aaa	aaa
aaa	aaa	aaa		bbb	bbb	bbb		aaa	aaa	aaa
aaa	aaa	aaa		bbb	bbb	bbb		aaa	aaa	aaa
								bbb	bbb	bbb
								bbb	bbb	bbb
								bbb	bbb	bbb
								bbb	bbb	bbb

Option Explicit
Sub MergeTwoArray()

    Dim ar1() As Variant
    Dim ar2() As Variant
    
    ar1 = Range("A1").CurrentRegion.Value
    ar2 = Range("e1").CurrentRegion.Value
    
    Dim rows As Long: Dim columns As Long
    
    rows = UBound(ar1, 1)
    columns = UBound(ar1, 2)
    
    ReDim Preserve ar1(500)  'Getting subscript out of range I cant increase existing size.
    Range("I1").Resize(rows, columns).Value = ar1  'Single Array I can paste using this line.
            
End Sub

Thanks
mg

 

[John_w]

You don't really need to merge them.

    Dim ar1() As Variant
    Dim ar2() As Variant
    
    ar1 = Range("A1").CurrentRegion.Value
    ar2 = Range("e1").CurrentRegion.Value
    
    Range("I1").Resize(UBound(ar1, 1), UBound(ar1, 2)).Value = ar1
    Range("I1").Offset(UBound(ar1, 1)).Resize(UBound(ar2, 1), UBound(ar2, 2)).Value = ar2