fill in missing years in a range

**keith_S** · 03-04-2010, 09:28 PM

hello! im just new to this forum, hope you guys can help me with this...
i need to be able to break down the inclusive years in a date range. my data appears as follows:
A B C D
dfs 1999 2001
dfs 1965 1968
dfs 1988 1988
xku 1998 2002
huu 2000 2010
huu 1985 1989

i need to make them appear as:

A B C D
dfs 1999 1999 2001
dfs 2000 1999 2001
dfs 2001 1999 2001

will appreciate it if u can send in a simple formula or code for visual basic ... thanks

**teylyn** · 03-04-2010, 09:33 PM

Hi keith, welcome to the forum.

Can you explain this a bit more? I don't see how the top leads to the bottom table. What is the logic? Are we looking at rows or columns?

The forum software is not very good at displaying tables with blanks, so you better attach a workbook with some sample data. You can upload a file by clicking "Go Advanced" below the Quick Reply box and then the paper clip icon.

**quekbc** · 03-04-2010, 09:34 PM

< deleted >

**keith_S** · 03-05-2010, 03:06 AM

Thank you teylyn!

I attached a sample as you've suggested..

The required outcome is a breakdown of the inclusive years of columns D and E on column G

i.e. E2:F2 = 1991 to 1993 = 1991, 1992, 1993

where, for every year that is greater than D but less than or equal to E, we insert a new row below the original row.

All contents of the original row are copied on the inserted rows except for the year which should increment downwards keeping account of the inclusive years on D (start year) and E (end year) in each range.

I used 'AddRows' to insert and copy but worked with G manually by getting the first year in each range and dragging it down where the value equals E.

I am working with a large database so I am hoping there is a way to do this programatically..

Many thanks.