# Thread: VBA: new 1-D array from selected index on 2-D array Thanks: 0 Likes: 0

1. ## Re: VBA: new 1-D array from selected index on 2-D array

Since you appear to be working with jagged arrays, I suggest you use a 1D array of arrays instead of a 2D array in the first place.  Reply With Quote

2. ## Re: VBA: new 1-D array from selected index on 2-D array Originally Posted by RoryA Since you appear to be working with jagged arrays, I suggest you use a 1D array of arrays instead of a 2D array in the first place.
Hi Rory,
. Thanks for the input.
. I do not quite follow ( NEI )
.. I guess you mean getting finally to this point?

Code:
```Sub Rory1DArrayofArrays()Dim OneDTwoDArr(1 To 3) As Variant

OneDTwoDArr(1) = Array("OneOne", "Onetwo", "OneThree")
OneDTwoDArr(2) = Array("TwoOne", "Twotwo")
OneDTwoDArr(3) = Array("ThreeOne", "ThreeTwo", "ThreeThree")

End Sub```
 From that point The OP has a simple method to get at his rows as per his request in Post #1 with something of the form ( as example for the second row )
OneDArr=OneDTwoDArr(2)
.
. However I do not see a simple way without looping in a complicated Function to create that 1D array of 2D arrays from his original 2 D Array.
.
. So I think he is back where he started? Or can You elaborate what You meant , bearing in mind the OPs original request? : - ( If understood the OPs initial request his start point was getting rows in a simple way from an existing 2 Dimensional Array , ( with the added requirement to ignore blanks in the rows ) )
. Originally Posted by EuroSong . trying to find an easy way of converting a selected index within a 2-dimensional array into a 1-dimensional array. For example, if I have the following:

TwoDArr(1,1)="OneOne"
TwoDArr(1,2)="OneTwo"
TwoDArr(2,1)="TwoOne"
TwoDArr(2,2)="TwoTwo"

... I want to do something like this:
OneDArr=TwoDArr(2): with the result being OneDArr(1)="TwoOne" and OneDArr(2)="TwoTwo". See what I mean?........Can anyone suggest a better way of doing this than the messy function I am using?
Alan  Reply With Quote

3. ## Re: VBA: new 1-D array from selected index on 2-D array

I meant he should skip creating a 2D array to start with, since it doesn't appear to be what he actually wants.  Reply With Quote

4. ## Re: VBA: new 1-D array from selected index on 2-D array Originally Posted by rorya i meant he should skip creating a 2d array to start with, since it doesn't appear to be what he actually wants.
o.k. , Thanks  Reply With Quote

5. ## Re: VBA: new 1-D array from selected index on 2-D array

Doc Alan - thank you so much for your input. However it appears as complicated (if not more so) than the way I'm already doing things anyway Rory - I think you're onto something there! I did not realise it was possible to create a 1D "array of arrays". This is exactly why I came to this forum: new ideas that may previously have been thought as impossible. I will try it. Seems like what I need. Something like this:

Code:
```Sub ArrayofArrays()
Dim MasterArray() As Variant
ReDim MasterArray(1 To 3)
Dim x As Integer
For x = 1 To 3
MasterArray(x) = GrabValues(x)
Next x
' And to test...
MsgBox UBound(MasterArray(1))
MsgBox UBound(MasterArray(2))
MsgBox UBound(MasterArray(3))
End Sub

Function GrabValues(x As Integer) As Variant
Select Case x
Case 1
GrabValues = Array("OneOne", "Onetwo", "OneThree")
Case 2
GrabValues = Array("TwoOne", "Twotwo")
Case 3
GrabValues = Array("ThreeOne", "ThreeTwo", "ThreeThree")
End Select
End Function```
Still interested in Rick's other methods though   Reply With Quote

6. ## Re: VBA: new 1-D array from selected index on 2-D array

Hi EuroSong. Originally Posted by EuroSong Doc Alan - thank you so much for your input. However it appears as complicated (if not more so) .......
. your welcome . The extra complication comes from the ability to select columns which I appreciate you do not need. But that helped me to get the other function, which as a one liner seemed similar to Ricks

.................. Originally Posted by EuroSong ......Rory - I think you're onto something there! I did not realise it was possible to create a 1D "array of arrays". This is exactly why I came to this forum: new ideas that may previously have been thought as impossible. I will try it......
. His idea made me think further as well.. Here, for example, what I was thinking if your start point was that you had a 2 Dimensional Array...

. ....This function would get your one dimensional Array of Arrays from your 2 dimensional Array ( Again ignore the additional Sub included for my later reference on how I got to the function ! )

Code:
```Function OneDArrayofArrays(Arr As Variant) As Variant
Dim OneDTwoDArr() As Variant: ReDim OneDTwoDArr(1 To UBound(Arr, 2))
Dim strtemp As String
Dim j As Long, i As Long
For j = 1 To UBound(Arr, 1)
For i = 1 To UBound(Arr, 2)
If Arr(j, i) <> "" Then Let strtemp = strtemp & " " & Arr(j, i)
Next i
OneDTwoDArr(j) = Split(Trim(strtemp), " ")
Let strtemp = ""
Next j
OneDArrayofArrays = OneDTwoDArr()
End Function
'
'
'
'
'
'
'
'

Sub OneDArrayofArraysfromTwoDArray()
'
Dim TwoDArr(1 To 3, 1 To 3) As Variant
TwoDArr(1, 1) = "OneOne"
TwoDArr(1, 2) = "OneTwo"
TwoDArr(1, 3) = "OneThree"
TwoDArr(2, 1) = "TwoOne"
TwoDArr(2, 2) = "TwoTwo"
TwoDArr(2, 3) = "" 'Blank to be ignored
TwoDArr(3, 1) = "ThreeOne"
TwoDArr(3, 2) = "ThreeTwo"
TwoDArr(3, 3) = "ThreeThree"

Dim OneDTwoDArr() As Variant: ReDim OneDTwoDArr(1 To UBound(TwoDArr(), 2)) 'Dimension OneDTwoDArr as 1 Dimension Array of size equal to rows of 2DArray
Dim strtemp As String 'Temp String for Row contents
Dim j As Long, i As Long
For j = 1 To UBound(TwoDArr(), 1) 'For each (Row) 1 D Array element)
For i = 1 To UBound(TwoDArr(), 2) 'For each column in 2 dimensional Array
If TwoDArr(j, i) <> "" Then Let strtemp = strtemp & " " & TwoDArr(j, i) 'Build string if not blank entry
Next i
OneDTwoDArr(j) = Split(Trim(strtemp), " ") 'Create 1 dimensional Array of Row elements in 2 dimensional Array and place in 1 Dimensional Array of Arrays
Let strtemp = "" 'Set temp String to null for next (row) loop
Next j

End Sub
'
'```
.......

And subsequently you obtain ( for example your original second ) Row in subsequent code in this form:

Code:
```Sub TestFunctionOneDArrayofArrays()

Dim TwoDArr(1 To 3, 1 To 3) As Variant
TwoDArr(1, 1) = "OneOne"
TwoDArr(1, 2) = "OneTwo"
TwoDArr(1, 3) = "OneThree"
TwoDArr(2, 1) = "TwoOne"
TwoDArr(2, 2) = "TwoTwo"
TwoDArr(2, 3) = "" 'Blank to be ignored
TwoDArr(3, 1) = "ThreeOne"
TwoDArr(3, 2) = "ThreeTwo"
TwoDArr(3, 3) = "ThreeThree"

Dim OneDTwoDArr() As Variant
Let OneDTwoDArr() = OneDArrayofArrays(TwoDArr)

Dim OneDArray As Variant
'Example for second Row
Let OneDArray = OneDTwoDArr(2)

End Sub```
............................... Originally Posted by EuroSong ......
Still interested in Rick's other methods though
. me too

Alan  Reply With Quote

7. ## Re: VBA: new 1-D array from selected index on 2-D array Originally Posted by EuroSong Still interested in Rick's other methods though  Originally Posted by DocAElstein . me too
Okay, here you go. If your values are always numeric or they are always text without spaces, or a combination of those two (the key being no spaces in any cells), then you can use this function...

Code:
```Function OneD(Arr As Variant, Index As Long) As Variant
OneD = Split(RTrim(Join(Application.Index(Arr, Index, 0))))
End Function```
If, however, the cells could possibly contain spaces, then you would use this function instead...

Code:
```Function OneD(Arr As Variant, Index As Long) As Variant
OneD = Split(Replace(Replace(RTrim(Replace(Replace(Join(Application.Index(Arr, Index, _
0), Chr(1)), " ", Chr(2)), Chr(1), " ")), " ", Chr(1)), Chr(2), " "), Chr(1))
End Function```  Reply With Quote

8. ## Re: VBA: new 1-D array from selected index on 2-D array Originally Posted by Rick Rothstein Okay, here you go. .........
.. Great Rick.
. - How you keep a clear head constructing those multiple ( nested ) replace functions is amazing..
. I have worked through , opening up the Functions to understand them. They work great ( obviously ! ).
. Thanks again
Alan

P.s. In case it helps anyone looking in, here are my opened up versions of Rick’s Functions along with a Test code

Code:
```'
'
'http://www.mrexcel.com/forum/excel-questions/867942-visual-basic-applications-new-1-d-array-selected-index-2-d-array-2.html#post4213878

Function OneDNoSpace(Arr As Variant, rwIndex As Long) As Variant
'OneDNoSpace = Split(RTrim(Join(Application.Index(Arr, rwIndex, 0))))
OneDNoSpace = Application.Index(Arr, rwIndex, 0) 'Slice Row( Includs space )
OneDNoSpace = Join(OneDNoSpace, " ") 'Make string from rowcontents with space between
OneDNoSpace = Trim(OneDNoSpace) 'RTrim(OneDNoSpace)'trim off last space
OneDNoSpace = Split(OneDNoSpace, " ") 'Make 1 dimensional array using split Function
End Function
'
Function OneDSpace(Arr As Variant, rwIndex As Long) As Variant
'OneDSpace = Split(Replace(Replace(RTrim(Replace(Replace(Join(Application.Index(Arr, rwIndex, _
0), Chr(1)), " ", Chr(2)), Chr(1), " ")), " ", Chr(1)), Chr(2), " "), Chr(1))
OneDSpace = Application.Index(Arr, rwIndex, 0) 'Make string from rowcontents with space between
OneDSpace = Join(OneDSpace, Chr(1)) 'Appears to join with two spaces ( Tab? )
OneDSpace = Replace(OneDSpace, " ", Chr(2)) 'replaces a space with something odd
OneDSpace = Replace(OneDSpace, Chr(1), " ") 'Replaces the ( Tab? ) with one space
OneDSpace = Trim(OneDSpace) 'RTrim(OneDSpace)'Take off last space
OneDSpace = Replace(OneDSpace, " ", Chr(1)) 'Replaces the space with a ( Tab? )
OneDSpace = Replace(OneDSpace, Chr(2), " ") 'Replaces the odd thing with a space
OneDSpace = Split(OneDSpace, Chr(1)) 'Make 1 dimensional array using split Function
End Function
'
Sub TestRick2()
Dim TwoDArr(1 To 3, 1 To 3) As Variant
TwoDArr(1, 1) = "OneOne"
TwoDArr(1, 2) = "OneTwo"
TwoDArr(1, 3) = "OneThree"
TwoDArr(2, 1) = "TwoOne"
TwoDArr(2, 2) = "TwoTwo"
TwoDArr(2, 3) = "" 'Blank to be ignored
TwoDArr(3, 1) = "ThreeOne"
TwoDArr(3, 2) = "ThreeTwo"
TwoDArr(3, 3) = "ThreeThree"
'Test OneDNoSpace
Dim OneDArr As Variant
Let OneDArr = OneDNoSpace(TwoDArr, 2)

'Test OneDSpace
TwoDArr(2, 2) = "Two Two" 'Case wanted space
Dim OneDArr2 As Variant
Let OneDArr2 = OneDSpace(TwoDArr, 2)
End Sub```  Reply With Quote

9. ## Re: VBA: new 1-D array from selected index on 2-D array Originally Posted by DocAElstein .. Great Rick.
. - How you keep a clear head constructing those multiple ( nested ) replace functions is amazing..
It is actually not that hard... you do not write it from left-to-right, rather, you construct the code line from the inside out, each intermediate constructed result being the argument for the next function (which you place around it). It involves a lot of jumping from front to back as you place each new function around the intermediate one you just constructed, but it is much easier to keep the steps and syntax straight when you do it that way. Originally Posted by DocAElstein . I have worked through , opening up the Functions to understand them. They work great ( obviously ! ).

OneDSpace = Join(OneDSpace, Chr(1)) 'Appears to join with two spaces ( Tab? )
No, it is not a tab nor two spaces. Chr(1) is a single character whose ASCII code is 1 and, likewise, Chr(2) is a single character whose ASCII code is 2... I don't know what those characters are, but I do know that they will not appear in the text being processed, so I cannot inadvertently end up replacing part of the real text while I am manipulating the text at each step. If I knew for a fact that, say, and asterisk (*) and an at-sign (@) would never appear in the text, I could replace each Chr(1) with "*" and each Chr(2) with "@" and the code would work the same, but since I have no idea what characters could make up the text, I chose to use characters I am nearly 100% positive the person creating the text would not know how to include within that text. As for why Chr(1) appears as multiple characters... it is just the way VB interprets when it tries to print it.  Reply With Quote

10. ## Re: VBA: new 1-D array from selected index on 2-D array Originally Posted by Rick Rothstein ..you do not write it from left-to-right, rather, you construct code line from inside out, each intermediate constructed result being the argument for the next function (which you place around it). It involves a lot of jumping from front to back as you place each new function around the intermediate one you just constructed, but it is much easier to keep the steps and syntax straight when you do it that way....
..

.... Thanks, I have done a similar sort of thing sometimes for much simpler examples, such as in the extra codes I presented here in the code windows containing my Functions. And I sort of “open up” your Functions working in reverse to this method.. but I still lack the experience to come up with the complicated Replace(Replace(Replace things that I have noticed is one of your specialities. Thanks for the valuable insight into your way of thinking. Originally Posted by Rick Rothstein .....a tab nor two spaces. Chr(1) is a single character whose ASCII code is 1 and, likewise, Chr(2) is a single character whose ASCII code is 2... I don't know what those characters are, but I do know that they will not appear in the text being processed, .....since I have no idea what characters could make up the text, I chose to use characters I am nearly 100% positive the person creating the text would not know how to include within that text. As for why Chr(1) appears as multiple characters... it is just the way VB interprets when it tries to print it.
.... Thanks that clears that up nicely – by googling I saw they had no text value, so with your explanation it all makes sense now.

................
. Thanks for coming back to the Thread again and sharing that extra info.

Alan Elston  Reply With Quote

## User Tag List

#### Tags for this Thread

array, dimension, vba #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•