VBA to Insert rows and filling cells with series and constants

[ExcelKook]

Hi all,

I have been working on this problem for a while but cannot find a solution myself. I have a sheet with columns for ID, NAME, YEAR, COUNT, CODE. In the final dataset I need rows for each year from 1995 to 2008 and all the other columns filled in as well (see outcome sheet in the example).

The problem is that for some of the entries in the dataset I only have observations starting from different years e.g. 2000 and for some of the entries one year in the series is missing e.g. 1995, 1996, 1998, 1999, 2000 (see example Sheet1). This means I need a macro to insert rows whenever there is either a year missing in a series or when the series does not cover the years 1995 to 2008. In addition, once the rows are inserted I need the ID, NAME, YEAR, COUNT and CODE data to be filled into the new rows.

I have posted and example workbook where Sheet1 contains the problem data and the outcome sheet contains the desired outcome data. RowInsertExample.xlsx

Thanks a lot for any ideas and help in advance.

ExcelKook

[JBeaucaire]

This should do it:

Please Login or Register  to view this content.

[jindon]

Here's my interpretation of your problem

Please Login or Register  to view this content.

[ExcelKook]

Hi JBeaucaire and jindon,

both your options work like a charm, thanks a lot. Unfortunately, I forgot to mention in my first post that starting from column F there is additional data in the sheet that should not be copied but the rows there should be left blank. Is is possible to update the VBA code to allow this. I have update the outcome sheet in my example file. RowInsertExample1.xlsx

Thanks a lot again!!

[jindon]

both your options work like a charm

Results are different.
Which is the correct result then?

[ExcelKook]

Results are different.

I can't see how the results are different. I like your option with the button and posting the outcome to a new sheet because this allows me to preserve the original data and it is very easy to use but the outcome seems to be identical if I am not mistaken.

[jindon]

My code determines the series of min(1995 in your example) to the max year of each ID, so the number of rows for each ID depends on the max for each ID.

Not the fixed series of 1995 to 2008 throughout the ID.

[ExcelKook]

You are right jindon, sorry that was sloppy checking of the results on my part. I apologize!

In my case I actually need the complete series from 1995 to 2008. Because your code only checks for the max year of each ID it does not insert a row for ID's that lack a row for 2008. This means that for you purposes JBeaucaire's solution provides the correct solution.

Maybe defining the maximum for the column rather than for each ID would solve the problem.

[jindon]

Then I will leave this to jb.

[JBeaucaire]

A few small tweaks:

Please Login or Register  to view this content.

[ExcelKook]

JBeaucaire, thanks a lot! That was a huge help!

When processing my data yesterday I noticed that for the year 2008 it would fill that ID into all the cells in the row apart for the year cell (which was set by Range("C" & RW + 1).Value = Yr). To solve the issue I just tweaked the code a tiny bit. See below in case anyone wants to reuse the code.

Please Login or Register  to view this content.