VBA concatenate two ranges and create new range in VBA-CodePudding

Is it possible to create a new range that concatenates the 3 ranges together into a new range? For example, the first value of the range would be The-Ball-Park and the second value would be The-Train-Station, so on and so forth over X amount of rows. If possible, I would like it so script would skip over completely blank rows.

I would like to save the range into a variable, I would Not like to have the range be inserted in the sheet.

Thank you!

Key1	Key2	Key3
The	Ball	Park
The	Train	Station
The	Fast	Lane

CodePudding user response：

You might need to make it a bit more clever if you don't intend on using exactly 3 inputs for each output. Let me know.

Option Explicit
Option Base 1
Sub ConcatRangeToArray()
    
    'Variables
    Dim I As Integer
    Dim OG_Array
    Dim Output_Array
    Dim RG As Range
    
    'Set this to whatever range you need concatenated
    Set RG = Sheet1.Range("A2:C4")  '<<< range to concatenate
    OG_Array = RG
    ReDim Output_Array(1 To UBound(OG_Array, 1))
    
    'Preform concatenation
    For I = 1 To UBound(OG_Array, 1)
        Output_Array(I) = OG_Array(I, 1) & "-" & OG_Array(I, 2) & "-" & OG_Array(I, 3)
        Debug.Print Output_Array(I)
    Next I

End Sub

Example of spreadsheet I used:

Example of output: