VBA Filling multi-area range with array skips every other range area

[tjdickinson]

In one sheet, I have essentially five tables stacked vertically with a spacer row between each table. (Table here meaning just a set of data, not an Excel Table.) I'm trying to fill the first and last columns of each table with the same array of times. In my code, I have declared the following variables:

Dim timeColA As Range: Set timeColA = Range("A2:A15, A19:A32, A36:A49, A53:A66, A70:A83")
Dim timeColU As Range: Set timeColU = Range("U2:U15, U19:U32, U36:U49, U53:U66, U70:U83")

Then I declare the array:

Dim timeFill As Variant: timeFill = VBA.Array("", "8:00", "8:20", "9:10", "10:00", "10:20", "11:10", "12:00", "12:30", "13:00", "13:50", "14:40", "15:00", "15:50")
timeFill = Application.WorksheetFunction.Transpose(timeFill)

And finally I fill the ranges with the array:

timeColA.Value = timeFill
timeColU.Value = timeFill

What I get, however, is the array correctly filled in A2:A15, A36:A49, A70:A83 (and the same for column U). It skips over A19:A32 and A53:A66.

Originally, I had one range containing A2:A15, U2:U15, A19:A32, U19:U32, etc. After running the macro, only column A was filled in, not column U. I thought it was having trouble filling two different columns, so I separated the ranges. But with the current results, I realise that it was simply skipping every other range area (thus, all the U ranges).

Can anyone explain to me why this is happening and how I can fix it?

Thanks in advance for your help!

 

[igold]

I can't explain why and I do not have a fix yet but it is not skipping the ranges. It is putting in the value of the first element of the Array in and repeating that element 14 times. In this case the first element is nothing ( "" ). If you change that to something else i.e. "7:00", you will see what is happening.

 

[BSALV]

     Dim timeColA As Range: Set timeColA = Range("A2,U2, A19,U19, A36,U36, A53,U53, A70,U70")
     Dim timeFill As Variant: timeFill = Application.Transpose(Array("", "8:00", "8:20", "9:10", "10:00", "10:20", "11:10", "12:00", "12:30", "13:00", "13:50", "14:40", "15:00", "15:50"))
     For Each c In timeColA.Cells
          c.Resize(14).Value = timeFill
     Next

 

[Fluff]

Another option

Dim Rng As Range
Dim timeColA As Range: Set timeColA = Range("A2:A15, A19:A32, A36:A49, A53:A66, A70:A83")
Dim timeFill As Variant: timeFill = VBA.Array("", "8:00", "8:20", "9:10", "10:00", "10:20", "11:10", "12:00", "12:30", "13:00", "13:50", "14:40", "15:00", "15:50")
timeFill = Application.WorksheetFunction.Transpose(timeFill)
For Each Rng In timeColA.Areas
   Rng.Value = timeFill
   Rng.Offset(, 20).Value = timeFill
Next Rng

 

[igold]

The workarounds are easy, can you explain why Excel is behaving as I explained in Post #2 from the original code...

 

[tjdickinson]

I can't explain why and I do not have a fix yet but it is not skipping the ranges. It is putting in the value of the first element of the Array in and repeating that element 14 times. In this case the first element is nothing ( "" ). If you change that to something else i.e. "7:00", you will see what is happening.

That's a very good observation, @igold, and very curious indeed. You're right--every-other range area, it fills the first array index into all the cells. What to me is even stranger (but maybe this is a better clue for someone who understands these things better than I) is that I have another similar operation that works correctly:

Dim lkNames As Range: Set lkNames = Range("A2:T2, A19:T19, A36:T36, A53:T53, A70:T70")
Dim lkInits() As Variant: lkInits = VBA.Array("", "BTH", "BHU", "DDU", "DHE", "DKE", "EGO", "FLE", "GAR", "HOV", "IPI", "LST", "MHLE", "MSU", "GVE", "SWU", "RRE", "SME", "TDI", "XFI")
lkNames.Value = lkInits

So here we have multiple range areas in lkNames and all of them fill correctly with the full array lkInits. So maybe the issue has something to do with the transposition of the array? That's really the only difference between the two--one of them fills columns while the other fills rows. Any ideas why this would be the case?

 

[tjdickinson]

     Dim timeColA As Range: Set timeColA = Range("A2,U2, A19,U19, A36,U36, A53,U53, A70,U70")
     Dim timeFill As Variant: timeFill = Application.Transpose(Array("", "8:00", "8:20", "9:10", "10:00", "10:20", "11:10", "12:00", "12:30", "13:00", "13:50", "14:40", "15:00", "15:50"))
     For Each c In timeColA.Cells
          c.Resize(14).Value = timeFill
     Next

Another option

Dim Rng As Range
Dim timeColA As Range: Set timeColA = Range("A2:A15, A19:A32, A36:A49, A53:A66, A70:A83")
Dim timeFill As Variant: timeFill = VBA.Array("", "8:00", "8:20", "9:10", "10:00", "10:20", "11:10", "12:00", "12:30", "13:00", "13:50", "14:40", "15:00", "15:50")
timeFill = Application.WorksheetFunction.Transpose(timeFill)
For Each Rng In timeColA.Areas
   Rng.Value = timeFill
   Rng.Offset(, 20).Value = timeFill
Next Rng

Thanks, @BSALV and @Fluff for the suggestions! Both work perfectly.

Unfortunately I'm only able to mark one response as a solution, even though both of you gave perfectly viable options. I'm ultimately going with Fluff's because I think it will be easier to update it in the future if the number of columns change--I only need to update the offset rather than changing the starting cell for each range.

Thanks again to both of you!

 

[Fluff]

Glad we could help & thanks for the feedback.

As for why it doesn't put the array into each range, I have no idea.