Slice the first dimension from 2D array

Question

I am trying to slice the first dimension of 2D array. The array variable is y (1 to 36, 1 to 19) I need to make it y(1 to 12, 1 to 19) To do such task I have to loop like that

    If rw > 0 Then

    ListBox1.AddItem
    ReDim v(1 To n, 1 To UBound(y, 2))

    Dim i As Long, j As Long
    For i = 1 To n
        For j = 1 To UBound(y, 2)
            v(i, j) = y(i, j)
        Next j
    Next i
    ListBox1.List = v()

    n = 0
End If

Is there an easier way to do such task without too much loops?

See if this helps .. https://stackoverflow.com/questions/175170/how-do-i-slice-an-array-in-excel-vba ... and ... https://stackoverflow.com/questions/18484818/looking-up-values-in-a-sliced-2d-array-excel-vba — Naresh, Apr 08 '20 at 13:09
I have already looked at it but these are too many solutions. I am looking for an easier approach to avoid using loops. As I am already used loops to solve the problem. — YasserKhalil, Apr 08 '20 at 13:11

score 1 · Answer 1 · answered Apr 08 '20 at 13:12

1

you could use Tranpose():

Dim v As Variant, v1 As Variant

v = Range("A1").Resize(36, 19).Value' just to fill a 36x19 array


v1 = Application.Transpose(v)
ReDim Preserve v1(1 To UBound(v, 2), 1 To 12)
v = Application.Transpose(v1)

answered Apr 08 '20 at 13:12

HTH

2,031
1
4
10

Thanks a lot. I thought of using Transpose but as you indeed knows it is limited. Is using Application,Index will be useful in this case and is it limited too?! – YasserKhalil Apr 08 '20 at 13:16
1

well, your question was about some 36x19 array, so no limitations occur for that case – HTH Apr 08 '20 at 13:18
I know. This is just a simple example so as to be able to follow the solutions. – YasserKhalil Apr 08 '20 at 13:23
I (but I think most, if not all, of us here) give answers to questions based on what's written in them. You should have addedd some caveat in this question of yours so as not to receive unuseful (for you) answers and give readers info to let them properly decide if they are able to answer or not – HTH Apr 08 '20 at 13:30
That being said, I know that `Application.Index()` can be of any use for slicing arrays, even in some articulated ways (with the use of `Evaluate`). But I'll leave it to you to find under what limitations (which I currently don't recall) and if they are _easy_ enough for your purposes – HTH Apr 08 '20 at 13:34
You are welcome. After what you'll consider a long enough awaiting, you could consider marking the answer as accepted if it soves the written question. Thank you – HTH Apr 08 '20 at 13:51
No problem at all. I consider it very good answer but I am seeking for more solutions if you don't mind of course. – YasserKhalil Apr 08 '20 at 13:52
1

Not at all, that's why I added _"After what you'll consider a long enough awaiting"_, since a marked answer could prevent people from digging into it. You may want to remove the mark and place it again afterwards, once you are quite sure you've received all the possible attention – HTH Apr 08 '20 at 13:56

score 1 · Accepted Answer · answered Apr 08 '20 at 14:18

1

You can slice an array as below. Most of this code is just to populate the first array so you won't need:

Sub x()

Dim v1(1 To 6, 1 To 3), i As Long, j As Long, v2()

'this loop is just to populate the first array
For i = 1 To UBound(v1, 1)
    For j = 1 To UBound(v1, 2)
        v1(i, j) = i * j
    Next j
Next i

'this is just to show the contents
Range("A1").Resize(UBound(v1, 1), UBound(v1, 2)) = v1

'this does the slicing (first three rows
v2 = Application.Index(v1, Evaluate("row(1:3)"), Application.Transpose(Evaluate("row(1:" & UBound(v1, 2) & ")")))
'this shows the sliced array
Range("F1").Resize(UBound(v2, 1), UBound(v2, 2)) = v2

End Sub

answered Apr 08 '20 at 14:18

SJR

22,986
6
18
26

1

Thank you very much for this amazing explanation. Just a question: Is using the INDEX will have limitations as Transpose or they both have the same limitations? – YasserKhalil Apr 08 '20 at 14:30
1

Sorry I don't follow your question. I will dig out some links on this method a bit later. – SJR Apr 08 '20 at 14:42
1

Here's one https://www.excelforum.com/tips-and-tutorials/758402-vba-working-with-areas-within-2d-arrays.html – SJR Apr 08 '20 at 16:32
There's another example here https://stackoverflow.com/questions/60816640/excel-vba-loop-through-10-000-sets-of-rows-each-set-containing-20-rows as well as a link in a comment to my answer from TM. – SJR Apr 08 '20 at 16:41
1

Thanks a lot for your great help. – YasserKhalil Apr 08 '20 at 16:51

Slice the first dimension from 2D array

2 Answers2