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

Feb 2 in Others 79 views

## 1 answer to this question.

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
``` • 63,000 points

## SQL - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

I ran into the same problem due ...READ MORE

## Excel - Make a graph that shows number of occurrences of each value in a column

There is probably a better way to ...READ MORE

## Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

## How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

## Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE