VBA - add multiple columns data into Single Array

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
976
Office Version
  1. 2010
Platform
  1. Windows
Hi Team,

I have below data in columns ("A:C:F"), I want to take all the values in a single Array,
Ar1. Could you plz assist how to achieve this task.

Data1Data2Data3
RickySachinJaysurya
GilchristViratKaluvitharna
McgrawKohliMendis
Shane WarneBumrah
Dhoni
My attempted code
Sub test_Array()
Dim ar1 As Variant
Dim ar2 As Variant
Dim ar3 As Variant

ar1 = Sheet1.Range("A2:a5").Value
ar2 = Sheet1.Range("c2:c6").Value
ar3 = Sheet1.Range("f2:f3").Value

MsgBox WorksheetFunction.CountA(ar1)

End Sub
 
Last edited:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Do you want it as a 1D or 2D array?
 
Upvote 0
How about
VBA Code:
Sub Mallesh()
    Dim Ary As Variant, Ary2 As Variant
    Dim r As Long, c As Long, nr As Long
    
    r = Range("A:F").Find("*", , , , xlByRows, xlPrevious, , , False).Row
    Ary = Application.Index(Range("A2:F" & r), Evaluate("row(1:" & r - 1 & ")"), Array(1, 3, 6))
    ReDim Ary2(1 To UBound(Ary) * UBound(Ary, 2))
    For c = 1 To UBound(Ary, 2)
        For r = 1 To UBound(Ary)
            If Ary(r, c) <> "" Then
                nr = nr + 1
                Ary2(nr) = Ary(r, c)
            End If
        Next r
    Next c
    ReDim Preserve Ary2(1 To nr)
    MsgBox Application.CountA(Ary2)
End Sub
 
Upvote 0
(y)So fast, OMG, Thank you so much for your help !!

I have one more question , if my data is in different sheet and in different column
What I need to change in above code.

say Sheet1.range("A2:a10")
Sheet2.range("c2:c20")
Sheet3.range("f2:f40")


Thanks
mg
 
Upvote 0
You would have to create 3 separate arrays & then loop through each one adding the items to another array.
 
Upvote 0
For your original question, here is another macro that you can consider...

Rich (BB code):
Sub Mallesh()
  Dim Arr As Variant
  Arr = Split(Join(Application.Transpose(Range("A2", Cells(Rows.Count, "A").End(xlUp))), Chr(1)) & Chr(1) & _
        Join(Application.Transpose(Range("C2", Cells(Rows.Count, "C").End(xlUp))), Chr(1)) & Chr(1) & _
        Join(Application.Transpose(Range("E2", Cells(Rows.Count, "E").End(xlUp))), Chr(1)), Chr(1))
  '
  ' Arr is a zero-based, one-dimensional array containing the names from the three columns
  '
End Sub


Note: Your posted data seems to be using Column A, C and E (not F), so I assumed Column E. If you really meant Column F, then change the two red highlighted E's above to F's.

This code is easily modified for your second question...
Rich (BB code):
Sub Mallesh()
  Dim Arr As Variant
  Arr = Split(Join(Application.Transpose(Sheet1. Range("A2", Cells(Rows.Count, "A").End(xlUp))), Chr(1)) & Chr(1) & _
        Join(Application.Transpose(Sheet2. Range("C2", Cells(Rows.Count, "C").End(xlUp))), Chr(1)) & Chr(1) & _
        Join(Application.Transpose(Sheet3. Range("E2", Cells(Rows.Count, "E").End(xlUp))), Chr(1)), Chr(1))
  '
  ' Arr is a zero-based, one-dimensional array containing the names from the three columns
  '
End Sub
Note: I assumed you meant to be using the Code Names for the sheets given that is what your examples used, so that is what I used as well.
 
Upvote 0
Superb Rick and Fluff, and millions of thanks for your help.?

thanks
mg
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
Hi Fluff and Rick,

Need one more help trying to understand the code , What is Char => Chr(1)) & Chr(1) is doing here,
Arr = Split(Join(Application.Transpose(Sheet1. Range("A2", Cells(Rows.Count, "A").End(xlUp))), Chr(1)) & Chr(1) &

if I want to add a text in place chr(1) and Chr(1) . what will be the text,

Thanks in advance for your help !!




Regards,
mg
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top