I have a huge range (80 K rows).
the values on that range are already sorted (from smallest to largest).
with the purpose to speed my code and also learning:
How to split that range into (4) even sequenced Arrays with condition that LBound / UBound is not duplicated on the other Arrays?
Note:that range has a lot of duplicate values ( I cannot delete it ,to preserve consistency of the other data).
In advance, thanks for any helpful comments ,Ideas and answers.

Sub Split_Range_into_4_even_sequenced_Arrays()
 
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim LastRow As Long: LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
 
   'Putting the whole range on one array,leads to the code takes a very long time to finish
     Dim wholearr:  wholearr = ws.Range("A1:A" & LastRow).Value2 '80K rows
      'My Code
 
   'The desired answer:Pseudocode
 
   Dim arr1, arr2, arr3, arr4         'Split the whole range into (4) arrays to make the code faster
    arr1 = ws.Range("A1:A" & UBound(arr1)).Value2                     '1st quarter of the range
       'My Code
    arr2 = ws.Range("A" & LBound(arr2) & ":A" & UBound(arr2)).Value2  '2nd quarter of the range
       'My Code
    arr3 = ws.Range("A" & LBound(arr3) & ":A" & UBound(arr3)).Value2  '3rd quarter of the range
       'My Code
    arr4 = ws.Range("A" & LBound(arr4) & ":A" & UBound(arr4)).Value2  '4th quarter of the range
       'My Code
End Sub

CodePudding user response：

Approach via jagged array

As your intention is to get 4 even structured arrays out of a given range (of e.g. 12 column elements), I'd suggest to calculate the needed number of rows per partial array first (see section b) in example code), where

• 12 is OP's rows count,
• -1 provides for correct increments,
• \ executes an integer division,
• 4 is the (constant) number of partial arrays needed,
• 1 will include also elements when rows count has division rests.

    elems = (12 - 1) \ 4   1                ' 11\4   1 = 2   1 ~~> 3            

instead of splitting an existing wholearray and to use a jagged array (aka as array of arrays) for better readibility (especially to avoid declaring more than 4 different variables arr1 .. arrN).

Btw Coding arr2 = ws.Range("A" & LBound(arr2) & ":A" & UBound(arr2)).Value2 etc would reference each partial range by "A1:A3" (assuming lower boundaries at 1).

Sub SplitRange()
    Const parts As Long = 4

    With Sheet1                               ' << e.g. project's sheet Code(Name)
    'a) Define needed range
        Dim lastRow As Long
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Dim rng As Range
        Set rng = .Range("A1:A" & lastRow)    ' << e.g. single column range
       'Set rng = .Range("A2:B" & lastRow)    ' << e.g. multicolumn range starting in row 2

    'b) calculate needed number of elements of each partial array
        Dim elems As Long
        elems = (rng.Rows.Count - 1) \ parts   1
        
    'c) Provide for a jagged array (aka array of arrays)
        'Single elements need a double indexation: jagged(part)(r,c)
        Dim jagged(1 To parts)

    'd) assign partial datafield arrays to jagged array    
    Dim part As Long
        For part = 1 To parts
            'get start row
            Dim start As Long
            start = (part - 1) * elems   rng.Row ' Rng.Row considers top cell
            'get (partial) source range
            Dim src As Range
            Set src = .Cells(start, rng.Column).Resize(elems, rng.Columns.Count)
            'assign 1-based 2-dim datafield to jagged array part 1..4
            jagged(part) = src.Value2
    
            'Debug.Print part, "Row " & start & ".." & ( start   elems - 1), src.Address
    
        Next part
    End With

End Sub