title: Slice array to use index on larger than 65000
body: <p>I use the code hereunder to calculate max values as described in this post (<a href="https://stackoverflow.com/questions/48461338/vba-max-value-of-group-of-values">vba max value of group of values</a>). The code works great but once I have more than 65k lines I get a data type mismatch when trying to pase the array:</p>
  
  <pre><code>sht.Range(Cells(1, lColumn), Cells(last.Row, lColumn)).Value = Application.Index(groupsArray, , lColumn)
  </code></pre>
  
  <p>Could somebody help me to slice the array in chunks. I have tried to get it working myself but without any luck.</p>
  
  <pre><code>    Sub FillGroupsMax()
          Dim lColumn As Long
          Dim sht As Worksheet
          Dim groupsArray As Variant    'array with all group infomation
          Dim groupsSeen As Variant    'array with group infomation already seen
  
          Application.ScreenUpdating = False    'stop screen updating makes vba perform better
  
          Set sht = ThisWorkbook.Worksheets("import")
          Set last = sht.Range("A:A").Find("*", Cells(1, 1), searchdirection:=xlPrevious)    'last cell with value in column A
          lColumn = sht.Cells(1, Columns.Count).End(xlToLeft).Column
  
          groupsArray = sht.Range(Cells(1, 1), Cells(last.Row, lColumn))
          'collect all the information on the Sheet into an array
          'Improves performance by not visiting the sheet
  
          For dRow = 2 To last.Row    'for each of the rows skipping header
  
              'check if group as already been seen
              If inArrayValue(Cells(dRow, 1).Value, groupsSeen) &gt; 0 Then
                  'if it has been seen/calculated attribute value
                  'Cells(dRow, 4).Value = inArrayValue(Cells(dRow, 1).Value, groupsSeen)
                  groupsArray(dRow, lColumn) = inArrayValue(Cells(dRow, 1).Value, groupsSeen)
              Else
                  'if it hasn't been seen then find max
                  'Cells(dRow, 4).Value = getMax(Cells(dRow, 1).Value, groupsArray)
                  groupsArray(dRow, lColumn) = getMax(Cells(dRow, 1).Value, groupsArray, lColumn)
  
                  'array construction from empty
                  If IsEmpty(groupsSeen) Then
                      ReDim groupsSeen(0)
                      'groupsSeen(0) = Array(Cells(dRow, 1).Value, Cells(dRow, 4).Value)
                      groupsSeen(0) = Array(groupsArray(dRow, 1), groupsArray(dRow, lColumn))
                      'attribute value to array
                  Else
                      ReDim Preserve groupsSeen(0 To UBound(groupsSeen) + 1)
                      groupsSeen(UBound(groupsSeen)) = Array(groupsArray(dRow, 1), groupsArray(dRow, lColumn))
                  End If
              End If
          Next
  
      sht.Range(Cells(1, lColumn), Cells(last.Row, lColumn)).Value = Application.Index(groupsArray, , lColumn)
          'reactivate Screen updating
          Application.ScreenUpdating = True
  
      End Sub
  
      Function getMax(group As String, groupsArray As Variant, lColumn As Long) As Double
  
          'for each in array
          For n = 1 To UBound(groupsArray)
              'if its the same group the Max we seen so far the record
              If groupsArray(n, 1) = group And groupsArray(n, lColumn - 1) &gt; maxSoFar Then
                  maxSoFar = groupsArray(n, lColumn - 1)
              End If
          Next
  
          'set function value
          getMax = maxSoFar
      End Function
  
      Function inArrayValue(group As String, groupsSeen As Variant) As Double
  
          'set function value
          inArrayValue = 0
          'if array is empty then exit
          If IsEmpty(groupsSeen) Then Exit Function
  
          'for each in array
          For n = 0 To UBound(groupsSeen)
              'if we find the group
              If groupsSeen(n)(0) = group Then
                  'set function value to the Max value already seen
                  inArrayValue = groupsSeen(n)(1)
                  'exit function earlier
                  Exit Function
              End If
          Next
  
      End Function
  </code></pre>