Split a range into (4) even sequenced Arrays with condition `LBound / UBound` is not duplicated on the other Arrays

Question

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

Answer 1

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

Answer 2

If you expect to produce an array and then to use its bounds for the initialization as in the next line, it's wrong:

arr = ws.Range("A" & LBound(arr) & ":A" & UBound(arr)).Value2

You'll get a brand new array with bounds from 1 to Ubound - Lbound + 1 instead of previous one.

As for the main question, I suggest to use a generator. I can see two ways: 1) to get sequence of (lower, upper) bound pairs, or 2) to get bounds one by one as lower1, upper1, lower2, upper2, .... I like the second approach:

Function GetQuarter(Optional MaxUBound)
' Generate a sequence of MaxUBound quoters starting from 1
' where MaxUbound is an integer number.
' Return the next bound when MaxUbound is missing.
' Example:
' GetQuarter(700) = 1
' GetQuarter = 175
' GetQuarter = 176
' GetQuarter = 350
' GetQuarter = 351
' ...
Const Parts = 4
Static Iter As Long
Static Max As Long
Static StepForward As Long
    If IsMissing(MaxUBound) Then
        Iter = Iter + StepForward
        StepForward = IIf(StepForward, 0, 1)
    Else
        Max = MaxUBound
        Iter = 0
        StepForward = 1
    End If
    GetQuarter = Int(Iter * Max / Parts + StepForward)
End Function

To start the generator call it with a parameter LastRow. To get the next bound you call it without a parameter. Here's an example, how it can be used:

Sub Test()
Dim arr1, arr2, arr3, arr4
Dim FirstRow&, LastRow&
Dim Source As Range
    FirstRow = 10
    LastRow = 20
    Set Source = Columns("A").Rows(FirstRow & ":" & LastRow)
    With Source
        arr1 = .Rows(GetQuarter(.Rows.Count) & ":" & GetQuarter).Value2
        arr2 = .Rows(GetQuarter & ":" & GetQuarter).Value2
        arr3 = .Rows(GetQuarter & ":" & GetQuarter).Value2
        arr4 = .Rows(GetQuarter & ":" & GetQuarter).Value2
    End With
    Stop
End Sub