Array to a range where the range has missed columns-CodePudding

I want to output an array to a range, however I want the range to be made up of several parts. My example code is below with the expected output.

Sub test()

 a = Array(1, 2, 3, 4, 5)

 Range("A2:B2,D2:F2") = a

End Sub

Output I Get:

A-B-C-D-E-F

1-2---1-2-3

Output I Want:

A-B-C-D-E-F

1-2---3-4-5

How do I achieve the output I need from the array? As you can see the output skips column C correctly but starts to output the array from the start again and not continue as I expected.

CodePudding user response：

Actually it does exactly what is expected. If you want to fill non-continous ranges you need to do it with each area:

Sub test()
    Dim Area1 As Variant
    Area1 = Array(1, 2)
    Range("A2:B2") = Area1

    Dim Area2 As Variant
    Area2 = Array(3, 4, 5)

    Range("D2:F2") = Area2
End Sub

CodePudding user response：

This isn't possible, sadly. You'll need to write code to write to a discontinuous Range the way you want. This has been discussed before on another site: https://www.mrexcel.com/board/threads/filling-a-non-contiguous-range-with-an-array-with-vba.763467/

This won't be valid for all situations of course, but if you know the sizes of the discontinuous ranges beforehand, you could store your info in multiple arrays that are appropriately sized, and then write to each range the data in the respective array. This way, you avoid the problem entirely.

If that's not possible, I would create a Sub that takes an array and all of the ranges as inputs, splits the array into as many appropriately-sized arrays as needed, and then write each of those arrays to the respective ranges.