Divide a range of values for each unique value

Question

I have an Excel data frame with structure similar to the exemple below:

Model	Year	Year
A	2010	2012
B	2013	2020

I need to split the range of years into a single row for each year. Example below:

Model	Year
A	2010
A	2011
A	2012
B	2013
B	2014
B	2015
B	2016
B	2017
B	2018
B	2019
B	2020

I couldn't think of anything to solve

Answer 1

With the next available row as the starting point, this will loop through each of the Models on SheetB and produce the distinct Model: Year combinations. As a result, if SheetB already has some data, this macro will add values underneath it rather than overwriting it.

---

Sub mydearmacro()

Dim a_ws As Worksheet: Set a_ws = ThisWorkbook.Sheets("SheetA")
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("SheetB")

Dim a_lr As Long, lr As Long
a_lr = a_ws.Range("A" & a_ws.Rows.Count).End(xlUp).Row


Dim model As String
Dim i As Long, y As Long, x As Long

For i = 2 To a_lr

    model = a_ws.Range("A" & i).Value
    start_year = a_ws.Range("B" & i) - 1
    y = a_ws.Range("C" & i) - a_ws.Range("B" & i) + 1
    lr = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    
    For x = 1 To y
        ws.Range("A" & lr + x).Value = model
        ws.Range("B" & lr + x).Value = start_year + x
    Next x
    
Next i

End Sub