I’m wondering if this same Array -> Range approach will work with a non-contiguous range?
For example we have an application at work that needs to write around 400 values into an Excel sheet (it does this via ActiveXObjects from the browser. Yuck, I know). At present it does this in a loop calling .cell(row, column) and setting the value property.
I imagine doing Range(“A1, B10, C15, etc”).value = SOME ARRAY, would be much quicker but I’ve been hunting the web for a while now and haven’t found anyone who has done this. Perhaps it’s just not possible?
There is absolutely, positively no way this would work. Except that it does. I have this
I want to write to A1, A2, and A5 without affecting A3 or A4.
Dim aWrite(1 To 5, 1 To 1) As Long
aWrite(1, 1) = 2
aWrite(2, 1) = 2
aWrite(5, 1) = 2
Union(Range(“A1:A2”), Range(“A5”)).Value = aWrite
And I get this
It doesn’t matter if I fill aWrite(3,1) or aWrite(4,1). In this example I left them blank. But if I fill them, it still leaves A3:A4 untouched. I didn’t know you could do that.