VBA - add multiple columns data into Single Array

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
853
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:

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,209
Office Version
  1. 365
Platform
  1. Windows
Do you want it as a 1D or 2D array?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,209
Office Version
  1. 365
Platform
  1. Windows
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
 

Mallesh23

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

ADVERTISEMENT

(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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,209
Office Version
  1. 365
Platform
  1. Windows
You would have to create 3 separate arrays & then loop through each one adding the items to another array.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,852
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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.
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
853
Office Version
  1. 2010
Platform
  1. Windows
Superb Rick and Fluff, and millions of thanks for your help.🕺

thanks
mg
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,209
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
853
Office Version
  1. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,126,896
Messages
5,621,495
Members
415,844
Latest member
Reda Fouad Ramzy

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
Top