Write to a non contiguous range with VBA

[Markus71]

Dear Users,

I am almost new to VB and I have been out there every night, since days, exploring the web to find a solution to write a range of horzizontal data to a multiple range of non contiguous cells.
After a while I had figured out, how to fill an array with the sourcerange, but pasting to the destinationrange which consists of contigous cells resulted always in the situation, that only the first entry of the array showed up in the destination range. Now, I have found a thread here, which helped me to construct a solution. The thread was >2000days old and the board recommended to start a new threat. Here I am.
Having said that, the solution I have now, works somehow, but it is really slow, because of the 2 loops I am using.

Basically I have a row of horizontal data with 344 cells. Due to the limitations in Range length I have splitted the range in 4x86 cells.
I take the 86 values in an array and then loop through the destination range.
I would appreciate if some pro can take a glance at it and provide me some support to make it faster.

Sub Test_Range3()
    Dim Sourcerng, Destinationrng As Range
    Dim rCell, acell As Range
    Dim i, n As Long

    Application.ScreenUpdating = False
    Set Sourcerng = Sheets("Database").Range("A1:CH1")
     n = Sourcerng.Cells.Count
        ReDim MyAr(1 To n)
        n = 1
        For Each acell In Sourcerng
            MyAr(n) = acell.Value
            n = n + 1
        Next acell
    
    i = 1
    Set Destinationrng = Sheets("Database").Range("L12:L14,L16:L20,L22:L26,L28:L30,L34:L35,M13,M16:M20,M29:M30,N13:N14,N16:N19,N22:N26,O13:O14,O16:O19,Q12:Q14,Q16:Q20,Q22:Q26,Q28:Q30,Q34:Q35,R13,R16:R20,R29:R30,S13:S14,S16:S19,S22:S26,T13:T14,T16:T19")
    For Each rCell In Destinationrng
        rCell.Value = MyAr(i)
       i = i + 1
    Next rCell
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

How long is the code taking? It works very fast with my test data. Even doing 4 sets of ranges like that is taking less than 0.05 seconds.
Do you have a lot of formulas that are dependent on the Destinationrng cells? If so, it should speed up some more if you add these lines near the start & end of the code.

Application.Calculation = xlCalculationManual
.
.
Application.Calculation = xlCalculationAutomatic

Because of the irregular nature of your destination range there won't be a great increase in speed of getting the results into it. The source range can be loaded into an array all at once instead of a cell at a time but that will not make a great deal of difference to the speed of the code given the relatively small size of the source range.

 

[Norie]

What limitation in 'range length' are you referring to?

 

[Peter_SSs]

What limitation in 'range length' are you referring to?

Given the address of the destination range, would seem pretty likely to be the 255 character limit for a range address?

 

[Markus71]

Thanks for the replies. The switch from Peter reduced the execution time from 1,06 down to 0,15s. Great!
The data is just numbers - in the 1,06s case the range data A1:CH1 was 1-86 - no formulas, nothing.

The number of characters are the problem Norie.
I have 8 zones of data and I can place only 2 zones in per execution in my array, otherwise the macro is not working (character length).
I would love to load all data in one shot into the macro and paste it to the following ranges:

Zone1   L12:L14,L16:L20,L22:L26,L28:L30,L34:L35,M13,M16:M20,M29:M30,N13:N14,N16:N19,N22:N26,O13:O14,O16:O19,
Zone2   Q12:Q14,Q16:Q20,Q22:Q26,Q28:Q30,Q34:Q35,R13,R16:R20,R29:R30,S13:S14,S16:S19,S22:S26,T13:T14,T16:T19
Zone3   V12:V14,V16:V20,V22:V26,V28:V30,V34:V35,W13,W16:W20,W29:W30,X13:X14,X16:X19,X22:X26,Y13:Y14,Y16:Y19
Zone4   AA12:AA14,AA16:AA20,AA22:AA26,AA28:AA30,AA34:AA35,AB13,AB16:AB20,AB29:AB30,AC13:AC14,AC16:AC19,AC22:AC26,AD13:AD14,AD16:AD19

In my test macro all the data is located on one sheet. In the original file , I have 4 Zones of data in sheet "A" and the same 4 zones of data in sheet "B", which I grab in four stages and store it in the database sheet. 344 cells of data.
The macro in this post pulls now two zones of data (out of 8 Zones) into the macro and saves them back to Sheet("A")

Has somebody an idea, how to bypass the character limitation to grab all the zone data in one shot, otherwise I have to execute the above code 4 times, which is probably not a good solution.
Thanks for help in advance.

 

[kvsrinivasamurthy]

Try this code

Sub Test_Range3()
    Dim MyAr
    Dim rCell As Range
    Dim i As Long
    Application.ScreenUpdating = false

 MyAr = Sheets("Database").Range("A1:CH1")
    i = 1
   
    For Each rCell In Sheets("Database").Range("L12:L14,L16:L20,L22:L26,L28:L30,L34:L35,M13,M16:M20,M29:M30,N13:N14,N16:N19,N22:N26,O13:O14,O16:O19,Q12:Q14,Q16:Q20,Q22:Q26,Q28:Q30,Q34:Q35,R13,R16:R20,R29:R30,S13:S14,S16:S19,S22:S26,T13:T14,T16:T19")
        rCell.Value = MyAr(1, i)
       i = i + 1
    Next rCell
Application.ScreenUpdating = True

 

[kvsrinivasamurthy]

One more code . Try both the codes and select suitable one from them.

Sub Test_Range4()
    
    Dim rCell As Range
    Dim i As Long
    Application.ScreenUpdating = False

  Sheets("Database").Activate
    i = 1
    
    For Each rCell In Range("L12:L14,L16:L20,L22:L26,L28:L30,L34:L35,M13,M16:M20,M29:M30,N13:N14,N16:N19,N22:N26,O13:O14,O16:O19,Q12:Q14,Q16:Q20,Q22:Q26,Q28:Q30,Q34:Q35,R13,R16:R20,R29:R30,S13:S14,S16:S19,S22:S26,T13:T14,T16:T19")
        rCell.Value = Cells(1, i).Value
       i = i + 1
    Next rCell
Application.ScreenUpdating = True

End Sub

 

[Markus71]

Thanks Murthy - The time with calculation manual off came down to 0,03 seconds. Good improvement and reduced code. Thanks for that.
The second example I do not understand, since i can't find the source range to process.

As on now, I just have the first two zones in the source range (A1:CH1) and in the destination range.
The big leap would be to read the data of all four zones into the array (A1: FP1) and write them to the non contiguous destination cells (Zone1, Zone2, Zone3, Zone4) (now I have only Zone 1 and Zone 2 in the destination range, due to character exceeding bottleneck .....).

Has anybody an idea? However, it is much faster now. Thanks Murthy.

 

[kvsrinivasamurthy]

In the 2nd code for each cell in the destination range value from the cells in 1st row is selected (cells(1,i))
Don't worry. Run the code. Note the time.
cells(11)=A1
cells(1,2)=B1
cells(1,3)=C1.........

 

[Markus71]

Thanks Murrhy, understood. I am not in front of my PC at the Moment to test, but it will be faster, I guess. Source range is solved since it can read almost unlimited values, but I have written now only half of the values in the destination range. How can I write the second half of the data now?
Thanks!