1

I have an array with values in it, and i want to paste several columns to two parts of the same sheet. First matrix would be pasted in column B and second in column H.

Is it possible to export several columns (not one and not all) with a single procedure? So far i have the options below, i can export one or all of the columns but not "a few".

Worksheets("Sheet1").Range("B" & m).Resize(s, 14).Value = Matrix3 'Exports entire matrix
Worksheets("E02 Sheet2").Range("B" & o).Resize(t, 9).Value = Application.Index(Matrix, 0, 1) 'Exports a single column of a matrix
Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
Carlsberg789
  • 145
  • 1
  • 11

1 Answers1

3

Something like that is possible: Let's assume we have a sheet like in the picture below enter image description here

The following code will put the first and third column into the variable vDat and write it back.

Sub TestIt()
    Dim matrix As Variant
    matrix = Sheet1.Range("A1:C3")

    Dim vDat As Variant
    vDat = Application.Index(matrix, Array(1, 2, 3), Application.Transpose(Array(1, 3)))

    Sheet1.Range("E1").Resize(3, 2).Value = Application.Transpose(vDat)

End Sub

You "only" need to modify the code to your needs

Storax
  • 11,158
  • 3
  • 16
  • 33
  • FYI - I listed the advanced possibilities of the `Application.Index` function like filtering and restructuring already in a 2018 post [Array insertion without loops or API calls](https://stackoverflow.com/questions/51688593/excel-vba-insert-new-first-column-in-datafield-array-without-loops-or-api-call/51714153#51714153); it's worth playing around with undiscovered or not so well known methods : +1) – T.M. Jan 11 '20 at 19:55