Iterating over a Union range

[gravanoc]

I have an issue iterating over a couple non-contiguous union ranges.
The reportRng is also non-contiguous, and could be specified as a union of B4:J4 & M4:N4. The problem is that trendRng.Cells(2, 1) will reference A3 instead of E7. The same happens with reportRng.
I think using Areas might help address the problem, but I'm not sure how.

    Set reportRng = individualLO.ListRows(1).Range.SpecialCells(xlCellTypeConstants)
    
    For i = 0 To x - 1
        
        With trendShts(i)
            Set trendRng = Union(.Range("A1:A2"), .Range("E7"), .Range("E17:E22"), .Range("E33:E34"))
        End With

        For j = 0 To trendRng.Areas.count - 1
            reportRng.Cells(i + 1, j + 1).Value = trendRng.Cells(j + 1, 1).Value
        Next j

        individualLO.ListRows.Add
        
    Next i

 

[RoryA]

This doesn't sound like a great data layout. Given that both ranges are discontiguous and they aren't even the same shape, I'd suggest you populate an array with the data from trendRng using a counter variable and a For Each cell in trendRng.Cells loop. Then do the same process in reverse with the output range using another For Each loop to iterate the cells and a counter to determine which array value to use.

 

[gravanoc]

This doesn't sound like a great data layout. Given that both ranges are discontiguous and they aren't even the same shape, I'd suggest you populate an array with the data from trendRng using a counter variable and a For Each cell in trendRng.Cells loop. Then do the same process in reverse with the output range using another For Each loop to iterate the cells and a counter to determine which array value to use.

Thanks, I will give it a try and let you know.