My code parses through a contiguous range of data in a spreadsheet (orng) and creates subranges from orng that contain the same string value (lot number) in the 6th column of orng. orng has 42 rows and 38 columns and each subrange has 1-15 rows and 38 columns.
As far as I know, I can't create a new range object for each subrange since the number of subranges is unknown. I've created an array that contains the data for the subranges (aData). I
have gotten it to work with the code below, but I feel like there is a much cleaner way of doing it that I can't figure out. I've also tried using a dictionary with no success. I will eventually have to call upon all the data for calculations and using multiple nested for loops to access each element seems convoluted.
I would prefer that the array was dynamic, but whenever I attempted the ReDim Preserve method the values would not be saved to the array. The size of the array would be correct, but every element was "Empty". According to Microsoft "each element of an array must have its value assigned individually" so it seems as though the only way I can keep the values when creating the array is to assign to each element.
After I found that webpage I implemented an array with a predetermined structure and the nested for loops. Is it possible to add the entire subrange to the array in one go? If not, what about a row?
Ideally, I could separate orng into different Areas, but since it is contiguous I am unable to do so (I'm not aware of a way to create Areas in a contiguous range).
What I'd like to know are:
is there a better way to do what I am trying to do (collection, dictionary, etc.)
if there is not a better way, can I get some advice on how to make this code cleaner (Easier to read, faster, less code, dynamic range, better structure)?
Private Sub rangetest() Dim twb As Workbook: Set twb = ThisWorkbook Dim cws As Worksheet: Set cws = twb.Sheets("Cleaned_2019+") Dim orng As Range Dim datelot As String, datelotcomp As String Dim c As Long, i As Long, j As Long, k As Long, numrows As Long, lastrow As Long, numlots As Long, _ curRow As Long, lotRows As Long, startRow As Long, layerRows As Long, aRow As Long Dim aLot() As Variant, aData(9, 49, 37) As Variant Dim Z As Boolean Set orng = cws.Range("A973:AL1014") 'Set initial range to work with. numrows = orng.Rows.Count 'Number of rows in orng. curRow = 1 'Current row in orng. startRow = 1 'Starting row in orng for next layer (changes when lot changes). i = 0 'Layer of array (for aLot and aData arrays). j = 0 'Row in orng where values for previous layer ended. Z = False Do Until Z = True datelot = Left(orng.Cells(curRow, 6).Value, 10) 'Lot that we want the data for. Corresponds to a layer in the aData array. datelotcomp = Left(orng.Cells(curRow + 1, 6).Value, 10) 'Lot of the next row in data sheet. If datelot <> datelotcomp Then 'If datelotcomp <> to datelot then we want a new layer for array. layerRows = curRow - j 'Number of rows for a particular layer ReDim Preserve aLot(i) 'Array of lot names aLot(i) = datelot 'Assign lot name to aLot array For aRow = 1 To layerRows 'Row index in array For lotRows = startRow To curRow 'Loops through orng rows and sets those values in array For c = 1 To 38 'Loops through columns. There are always 38 columns aData(i, aRow - 1, c - 1) = orng.Cells(lotRows, c).Value 'Add values to each index in array Next c Next lotRows Next aRow j = curRow i = i + 1 startRow = curRow + 1 End If If curRow = numrows Then 'End loop at end of orng Z = True End If curRow = curRow + 1 Loop numlots = i End Sub
The result I get is an array with the structure aData(9, 49, 37) that contains data in the first 4 layers aData(1-3, , ). This corresponds with the unique number of lots (criteria from column 6 of orng) so the code is working correctly. I'd just like advice on if I'm doing anything inefficiently.
I will be checking back to answer questions or to add clarification.
The orng size will change based on user input. I have the user inputting a start and end date and orng is created based on those values. Once I have the subranges from orng I will then use other criteria from other columns of the subrange to determine which rows to apply calculations to. The end result will be the lot number(s) with the calculations for the lot(s) printed out for the user.