Pasting certain columns from an array into 1 range

Question

Is there a way to copy certain columns from 1 array into 1 range. My problem is that not only the first few columns I want to omit, which should be easy, but also one column in the middle. However I need that column in the middle to validate against. Is there an easy way to skip that column when copying a row from the array into a worksheet? Or is the only way around to make that column the first or the last column when building the array?

The code I have until now is:

For row1 = 2 To TotalRowsMerged
    For row = 2 To TotalRowsAgron
        If Cells(row1, 1) = Agron_Array(row, 1) And Cells(row1, 2) = Agron_Array(row, 2) Then
            Range(Cells(row1,11).Address,Cells(row1,23).Address) = Agron_Array(row,

The columns that I need are: 5 to 13 and 15 to 18, so in total 13, which is the same size as my range.

Rory · Accepted Answer · 2015-07-22 09:35:27Z

1

You can use Index with an array of column indices:

Range(Cells(row1,11).Address,Cells(row1,23).Address) = _
Application.Index(Agron_Array, row, Array(5, 6, 7, 8, 9, 10, 11, 12, 13, 15, 16, 17, 18))

edited Jul 22, 2015 at 9:35

answered Jul 22, 2015 at 9:25

Rory

34.2k5 gold badges40 silver badges42 bronze badges

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

3 Comments

Luuklag Over a year ago

Range(Cells(row1, 11).Address, Cells(row1, 23).Address) = Application.Index(Agron_Array(row, Array(5, 6, 7, 8, 9, 10, 11, 12, 13, 15, 16, 17, 18)))

Luuklag Over a year ago

This is what I made out of it, as it was expecting those brackets after Index, however I get an Run-time error 13: Type mismatch now.

Rory Over a year ago

Sorry - I just corrected the typos in my answer. Please try the amended code.

Collectives™ on Stack Overflow

Pasting certain columns from an array into 1 range

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related