Slicing A 2d Array

  • I have an array I loaded from a file







    the above is contained in an array called lnarr

    the code:

    Dim sv As Variant
    Dim tfn() As Variant
    Dim t1()
    For i = 0 To UBound(lnarr) - 1
      sv = Split(lnarr(i), ",", , 1): dt = sv(0)
      sw = awf.Index(sv, 0, Array(2, 3, 4))  'awf=application.worksheetfunction
      If dt = "I" Then j1 = j1 + 1: ReDim Preserve t1(j1): t1(j1) = sw
    Next i

    The above code does exactly what i want it to.

    it retrieve only columns 2,3,4






    when i load the array without splitting it by col,


    For i = 0 To UBound(lnarr) - 1
      sv = Split(lnarr(i), ",", , 1): dt = sv(0)
      If dt = "I" Then ac(1) = ac(1) + 1: ReDim Preserve t1(ac(1)): t1(ac(1)) = sv
    Next i

    The code above produces

    an array with the following

    t1(0)(0)= "I"

    t1(0)(1)= "Doors"

    t1(0)(2)= "Y"

    t1(0)(3)= 1

    t1(0)(4)= 2

    t1(0)(5)= 3

    t1(1)(0)= "I"

    t1(1)(1)= "cows"

    t1(1)(2)= "M"

    t1(1)(3)= 11

    t1(1)(4)= 21

    t1(1)(5)= 31

    t1(2)(0)= "I"

    t1(2)(1)= "Horses"

    t1(2)(2)= "Y"

    t1(2)(3)= 111

    t1(2)(4)= 112

    t1(2)(5)= 113

    when i try to extract columns 2,3,4 from this array,

    it only retrieves the first row (t1(0) (2,3,4))

    t2= application.worksheetfunction.index(t1,0,Array(2,3,4))
    'tried this to
    t2= application.worksheetfunction.index(t1,0,application.worksheetfunction.Transpose(Array(2,3,4)))

    it seems i can extract cols 2,3,4 when i loop through each row

    i thought application.worksheetfunction.index(t1,0,Array(2,3,4))

    should retrieve cols 2,3,4 in all rows

    What am i missing?

  • Not entirely sure what your aim is, but you can retrieve certain columns from an array like

       Dim a As Variant, b As Variant
       a = Range("A1:Z20").Value
       b = Application.Index(a, Evaluate("row(1:" & UBound(a) & ")"), Array(2, 3, 4))
  • Fluff13,

    i am not using a range. this is entirely within the vba array

    i want to split the columns after the array is loaded

  • Fluff13,

    i have attached a workbook and a test file.

    put file in same location as workbook.

    open work book view logdata macro - put breakpoint where indicated

    run macro -hold mouse over each array var for contents

    do the same with logdata1 macro

    the difference is the way it extracts the columns

    logdata macro loops and extracts row by row cols 2,3,4 and puts into t1()

    logdata1 macro loops and put entire contents row by row into into t1()

    then tries to extract cols 2,3,4 from the array t1 using


  • I'm still none the wiser, about what you are trying to do, but as far as I know you cannot use Index on a jagged array the way you are trying to do.

  • @ rabsofty


    I am not quite sure what you are missing, because I am not quite sure what your thinkings are behind what you were trying to do. You may be getting slightly mixed up with slicing an array (https://usefulgyaan.wordpress.…t-loop-application-index/ ) and doing the the “Index(array(), Rws(), Clms()) ” type of formula mentioned in Fluff’s post # 2 , Slicing A 2d Array

    ( Having said that, there is no reason why you should not use combinations of those 2 techniques… )

    I thought initially I could actually do what you wanted, but then on closer inspection of what you actually want , I found I could do it, but probably not quite as simply neatly, or dynamically as you would like.._

    _... Like Fluff I got a bit lost in your long macros in the uploaded file. You seem to be doing everything in an untypical long way around. Or maybe it is just a way that I am unfamiliar with. But It is interesting following through your uploaded files, since you have 'commented it very well, but it takes a while. ( a small typo: your macros look for a logs.dta file, - it should be logs.txt )

    But anyway, if I have understood correctly, it all boils down to

    _a) you have a “jagged” array, that is to say an array containing arrays as elements – an array of arrays.


    _b) you want to use that often seen “Index(array(), Rws(), Clms()) ” type of formula, ( or a combination of that and the slicing idea) , to have the same “jagged” array as output but with “column” elements reduced in the arrays that are the elements of the main “outer” array

    ( It threw me off for a while you asking for getting specific columns, as did also throw me off your Thread title containing 2d array, since , initially anyway, we are looking at 1 Dimensional arrays…. Or as it finally comes out, maybe we aint…

    Let me try to explain a bit more

    _a) you end up at a point in your macro Sub logdata1() , with an array of arrays, t1

    That is to say, a main array t1, whose elements are themselves, arrays. Let me call those the “Inner” arrays. ( I think what you end up with is sometimes called, as suggested, a jagged array. I suppose in your case we might call it an “unjagged” jagged array, since you have the same number of elements in each of the “inner” arrays . )

    Showing it pseudo pictorially, simplified, you end up with something like this

    a b c d e __ f g h i j __ k l m n o

    In that simplified example, you have a main 1 D array with three “inner” 1 D arrays in it. Each “inner” array has 5 elements in it. ( I guess we could those 5 elements 5 “columns”, but that is a debatable point in a 1D array )

    _b) What you want, ( which took me a while to realise, ) is to have a simple code line, without the need to do any looping to get an output, also a “unjagged” jagged array, like, pseudo

    b c d __ g h i __ l m n

    Loosely speaking, to offend and upset the academics, we can say you have picked out columns 2 , 3 and 4

    You wanted to do that like in this code line of yours

    tfn = awf.Index(t1, 0, Array(2, 3, 4))

    As you found, it did not work, and I have not been able to figure out yet how to do it exactly in the way you tried with a simple “Index(array(), Rws(), Clms()) ” type code line..


    It seems as if Excel holds a jagged array like yours somehow as if it was actually a 2D array like, pseudo pictorially

    a b c d e

    f g h I j

    k l m n o

    At any rate, Index seems to handle it as if it were that 2D array

    So you can get the answer you wanted like this

    tfn = Array(awf.Index(t1, 1, Array(2, 3, 4)), awf.Index(t1, 2, Array(2, 3, 4)), awf.Index(t1, 3, Array(2, 3, 4)))

    It gets your answer without looping, but the problem is that it is not dynamic, and I can’t see yet an easy way to make it dynamic


    Out of passing interest, I was surprised to find that Excel was apparently holding such a jagged array as if it was 1 2D array. . I did not expect that. I expect other people wouldn’t?

    Out of interest I would welcome any comments on that from people familiar with these Index way of doing things to return parts of an array. Maybe someone else noticed it.

    BTW , I can think of one quite useful application of it. …. The following…

    There is a fairly common way of importing a text file into an array and/ or into a spreadsheet: .. it is like this…A single complete text string of the text file is first put into a simple string variable. Then

    _(i) That string is split by the line separator to get a 1 D array of all the lines ( “rows” )

    _(ii)a That array is looped through , and at each loop we split again each element by the value separator to get a 1D array of the “columns”, and then further

    _(ii)b at each loop we loop again along the elements ( “columns” ) – At each of these “inner” loops we fill either a spreadsheet or an array with an element

    What we can do is do away with the inner loop _(ii)b and instead at _(ii)a just fill a 1 D array with the 1 D array of the “columns”

    Then finally, to get our array / range we apply a code line of pseudo like

    Index(OurUnjaggedJaggedArray() , {1\2\3 …..} , {1,2,3 …..})

    ( Of course you can pick out whatever set of rows or columns that you want instead )

    I expect that might be a bit more efficient, but I am not sure.

    In the uploaded file, I have put two example text import macros which work on your logs.txt file saved at the same place as the file. One does the more typical way, the other demonstrates the way I tried to explain…

    Neither macro is particularly efficient, - I am mainly trying to demo what I was on about


  • then tries to extract cols 2,3,4 from the array t1 using


    You can't use 0 for the row argument, you need an array of row numbers - for example:

    tfn = awf.Index(t1, Evaluate("ROW(A1:A" & UBound(t1) + 1 & ")"), Array(2, 3, 4))

    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • You can't .........

    Sn-ap :)

    Hi Rony. You possibly missed the point , like I did on first read.

    The OP actually has in t1 a 1 D array. In that array he has 5 elements. Those elements are themselves 1 D arrays with 6 elements in them. ( Maybe you might call that an “Unjagged” jagged array?,I am not too sure what the definition is of a jagged array)

    What the OP wanted to do is, rather than looping, to apply a Index type thing to that t1 1 D array of 1 D arrays, so as return him a similar 1 D array of 1 D arrays but with just 3 of the previous 6 elements in the “inner” arrays.

    I was surprised to find that you can actually do that, all be it in a limited way. .._

    _... The thing what surprised me is that it appears that Index treats that t1 1D array of 1 D arrays as if it was a 2 D array. So that means you can in fact apply that code line like you and Fluff suggested to get what he wanted. Its all in my post


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!