3

I have a 2-day array like the following:

myArray(1 to 100, 1 to 3)

My normal way to paste an array is like this:

With ActiveWorkbook.Worksheets("Semesters")

If IsEmpty(.Range("A2")) Then
    .Activate
    .Range("A2", "C2").Resize(UBound(myArray)).Value = myArray
Else
    .Activate
    .Range("A1").End(xlDown).Offset(1, 0).Activate
    Range(ActiveCell, ActiveCell.Offset(0, 2)).Resize(UBound(myArray)).Value = myArray
End If

End With

But this pastes the entire array. I want to paste all of the first dimension, but only values at index 2 and 3 of the second dimension. Can this be done without creating another array that filters out index 1 of dimension 2, and if so, how?

Jwok
  • 646
  • 9
  • 23
  • Check [how to slice array here](https://stackoverflow.com/questions/175170/how-do-i-slice-an-array-in-excel-vba) – L42 Jan 10 '18 at 02:13

1 Answers1

3

You cannot do this in one step but you can do it in two.

.Range("A2").Resize(UBound(myArray, 1)) = application.index(myArray, 0, 2)
.Range("C2").Resize(UBound(myArray, 1)) = application.index(myArray, 0, 3)
  • I think it should be `Application.WorksheetFunction.Index(myArray, 0, 2)`. Else it will return only the first elements. – L42 Jan 10 '18 at 02:36
  • 2
    I dunno, I ran the code and it put all the first rank elements of a single second rank into my destination. –  Jan 10 '18 at 02:39
  • 1
    Yeah, run that same code too and get all the 1st element only on the destination cells. What I did is: `.Range("A2").Resize(UBound(myArray, 1)) = application.index(myArray, Evaluate("ROW(1:" & UBound(myArray, 1) & ")"), 2)` to make it work. Anyways, if it works for the OP then no worries ;) – L42 Jan 10 '18 at 03:19
  • Thanks for the confirmation @sktneer. L42 is probably right from his/her perspective; we are just tackling two different sets of data from two different sides since the OP supplied none at all to support his/her claim. –  Jan 10 '18 at 09:01
  • Thanks all, this is very helpful. Is there any way to do something like `range = application.index(myArray,0, 2-3)` ? – Jwok Jan 10 '18 at 19:52