Populate 1D Array from 2D range Using Cell Property

rkaczano

Board Regular
Joined
Jul 24, 2013
Messages
77
I have a 2d Range in Excel that I want to pass to a 1D array in VBA. I want to build the 1d Array based on incrementing through each column of each row. The code I have is working but it is building the array based on incrementing the rows of each column. I want the opposite but cannot figure out how to edit the code.

2, 4, 6, 8,
3, 6, 9, 12
5, 10,15, 20

What I want:
2
4
6
8
3
6
9
12

What my code is doing:
2
3
5
4
6
10


VBA Code:
Option Explicit
Sub Convert()
Dim Vector
Dim k As Integer
Vector = Create_Vector(Sheets("Sheet1").Range("A4:D8"))
For k = 1 To UBound(Vector)
        Sheets("Sheet1").Range("B20").Offset(k, 1).Value = Vector(k)
Next k
End Sub
*****************
Function Create_Vector(Matrix_Range As Range) As Variant
Dim No_of_Cols As Integer, No_Of_Rows As Integer
Dim i As Integer
Dim j As Integer
Dim Cell
No_of_Cols = Matrix_Range.Columns.Count
No_Of_Rows = Matrix_Range.Rows.Count
ReDim Temp_Array(No_of_Cols * No_Of_Rows)
'Eliminate NULL Conditions
If Matrix_Range Is Nothing Then Exit Function
If No_of_Cols = 0 Then Exit Function
If No_Of_Rows = 0 Then Exit Function

For j = 1 To No_Of_Rows
    For i = 0 To No_of_Cols - 1
    Temp_Array((i * No_Of_Rows) + j) = Matrix_Range.Cells(j, i + 1) 'was Temp_Array((i * No_Of_Rows) + j) = Matrix_Range.Cells(j, i + 1)
    Next i
Next j
Create_Vector = Temp_Array
End Function


Thanks
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
When posting code, please use code tags - see my signature block below for details. I have fixed your post for you this time.

See if altering the double loop in your function to this does what you want.

VBA Code:
For j = 0 To No_Of_Rows - 1
    For i = 1 To No_of_Cols
    Temp_Array((j * No_of_Cols) + i) = Matrix_Range.Cells(j + 1, i)
    Next i
Next j
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,128,128
Messages
5,628,864
Members
416,346
Latest member
Sekolaine

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