Dynamically and condtionally divide a dataset into multiple arrays or store information regarding split locations

Question

I have a set of data that is sorted on the column "Year". I started with a small set to build my code, so there are only around 500 rows. There are about 15 different values in the column "Year" and I want to make a graph for each column separately. To do this I need to know at which row one year starts and on which row it ends. Therefore I either need the large set of data divided into separate arrays per year, or an array that contains the start and end rows per year, so that I can reference them in my code that produces the graphs.

I was thinking of writing a loop that checks whether or not the value for a row in column "Year" is different from the value above, using something like this:

For row = 3 to TotalRows
    IfNot Cells(row,1).Value = Cells(row-1,1).Value Then
        'Row = startrow current year
        'Cells(row,1).Value = current year
        'Row -1 = endrow previous year
        'Cells(row-1,1).Value = previous year
    End If
Next row

What would be the best way to store these values, so that I can reference them later?

Answer 1

I just got drawn back to this question by earning the tumbleweed badge. However I recently worked my way around this problem, so I thought I might just as well share my answer with anyone that might come across this:

Public MyRange As Range
Public Years() As Variant
Global TotalRows As String

Sub DevideData()

Dim i As Integer

Set MyRange = Range("AL35:AN35") 'output data from Years here for manual control.

ReDim Years(1, 2)

Years(0, 0) = Cells(2, 1).Value
Years(0, 1) = 2

i = 1
ThisWorkbook.Worksheets("Simple Boundary").Activate
TotalRows = ThisWorkbook.Worksheets("Simple Boundary").Range("A100000").End(xlUp).row

For row = 3 To TotalRows 'my data starts in row 2, as row 1 is a header
    Years = ReDimPreserve(Years, i, 2) 'as data starts in row 2 this is the first entry

    If Not Cells(row, 1).Value = Cells(row - 1, 1).Value Then 
'check if cell value differs from row above if so fill a line in the array
        Years(i - 1, 2) = row - 1
        Years(i, 0) = Cells(row, 1).Value
        Years(i, 1) = row
        i = i + 1 'increase the row of the array
    End If
Next row

Years(i - 1, 2) = TotalRows
MyRange.Resize(i, 3) = Years

End Sub

Also more elegant answers can be find in another question of mine here