VBA array exporting to sheet

Question

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

Storax · Accepted Answer · 2019-12-29 08:57:34Z

3

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

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

edited Dec 29, 2019 at 8:57

answered Dec 29, 2019 at 8:51

Storax

12.7k3 gold badges21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

T.M. Over a year ago

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; it's worth playing around with undiscovered or not so well known methods : +1)

Collectives™ on Stack Overflow

VBA array exporting to sheet

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related