Transfer multiple ranges to one array

[shum2934]

Hi,

I have a large table of data (from columns A to AU with about 7000 rows). I'd like to put the data from columns A, B, C, I and AU only in an array using a macro.

I've done a lot of searching and found how to put one range of data into an array, but not multiple ranges.

I understand I could do this by looping through thousands of lines of data, but surely there is a more efficient way than this!

This is my attempt but it keeps highlighting the '=' and saying "Expected: end of statement".

Sub datacheck()
Dim xarray(0 To 10000, 0 To 4) As Variant
 
Sheets("data").Activate
 
ReDim Preserve xarray(0 To Range("a50000").End(xlUp).Row - 4, 0) = _
range("a4", range("a50000").End(xlUp)).Value
 
End Sub

Any help would be greatly appreciated!

Sam

[I am using Excel 2010 on Windows XP]

 

[pgc01]

Rick, thank you for adding your comments that will surely prove useful to others when using this approach.

I'll add also that one should not forget that Index() can be used to access a multi-area range.

For ex., if we have a range that consists of some scattered cell, we can use Index() to load their values into an array.

Dim vArr As Variant
Dim r As Range
 
Set r = Range("A1,C4,F9,G2")
 
vArr = Application.Index(r, 1, 1, Array(1, 2, 3, 4))

... and other parameter combinations...

 

[Peter_SSs]

Thanks also from me to both Pedro and Rick. I'm struggling to fully take it in but I hope I will find occasion to come back to this thread sometime to put some of it into action. Thanks again.

 

[Mr Boris]

Here is a function that will return a one-dimensional array for the specified list of columns on the specified worksheet where the data's starting row is specified...

Function GetArrayFromColumns(SheetName As String, StartRow As Long, ParamArray Cols()) As Variant
  Dim LastRow As Long, C As Variant, Text As String, WS As Worksheet, MyArray() As String
  If SheetName = "" Then SheetName = ActiveSheet.Name
  Set WS = Worksheets(SheetName)
  For Each C In Cols
    LastRow = WS.Cells(Rows.Count, C).End(xlUp).Row
    Text = Text & Chr(1) & Join(WorksheetFunction.Transpose(WS.Range(WS.Cells(StartRow, C), WS.Cells(LastRow, C))), Chr(1))
  Next
  GetArrayFromColumns = Split(Mid(Text, 2), Chr(1))
End Function

You can pass in the empty string ("") for the SheetName argument if you want the code to execute against the ActiveSheet. Here is a sample macro that shows this function in use...

Sub Test()
  Dim Z As Long, MyArray() As String
  MyArray = GetArrayFromColumns("Sheet1", 2, "A", "B", "C", "I", "AU")
  For Z = LBound(MyArray) To UBound(MyArray)
    Debug.Print MyArray(Z)
  Next
End Sub

This macro will run against Sheet1, it will assume the data starts on Row 2 and it will process all the data in Columns A, B, C, I and AU printing out the contents of the returned array to the Immediate Window inside the VB editor. You can declare your array (the one that receives the output from the function) as Variant or as String() only... the elements of the returned array will all be String values (even if the cells in the specified columns contained real Excel numbers).

Thanks Rick... an old post but perfect for my needs today! Thanks for posting!

 

[OriginalAccess]

I'm not sure if you are referring to efficiency of writing the code or its run-time. I also am not sure if you were contemplating looping through the worksheet "lines of data" or array "lines of data".

This is not an area I'm strong in so I'm happy to have comments from Dave/Rick (or anybody else ) but even with looping, this code took approximately 0.08 seconds to process your desired columns of 7,000 rows into xarray.

Option Base 1

Sub DataToArray()
    Dim xarray, tmparray, myCols
    Dim i As Long, j As Long, LR As Long, c As Long, y As Long
        
    myCols = Array(1, 2, 3, 9, 47) '<-Cols A,B,C,I,AU
    
    y = UBound(myCols)
    LR = Range("A" & Rows.Count).End(xlUp).Row
    ReDim xarray(0 To LR - 1, 0 To y - 1)
    tmparray = Range("A1").Resize(LR, myCols(y)).Value
    For i = 1 To y
        c = myCols(i)
        For j = 1 To LR
            xarray(j - 1, i - 1) = tmparray(j, c)
        Next j
    Next i
End Sub

Nice code, but slow. I run a timing test and loading 3 ranges and ignoring the middle

Dim avar3Col() as Variant
avar3Col = Range("A3000:C3000).Value
​

takes 12 ms.
Your code takes 160 ms
An intermediate solution (86 ms) would be to load two arrays for columns A and C, and then move them to common array using for..next. The question is why would you want to… It's faster to load all 3 ranges, and then move 2. The less you ask Excel to access a cell or a range, the faster it will be.

 

[Peter_SSs]

@ OriginalAccess
Interesting that you have responded to a thread nearly 7 years old and to a specific post that clearly was not the best one in the thread.

It's faster to load all 3 ranges, and then move 2.

Have you tested your faster code against that suggested by pgc01 in post#7?
If so, and it is faster, many readers would benefit by you posting your actual code that answered the original question in the thread, rather than just an assertion about a general approach.

 

[dckmob]

Bookmarked this thread a long time ago, knowing that today would come. Thanks guys!