Insert New Rows Between Existing Cells With Missing Sequential Dates

Hello members,
I've read all answers about the thread in this page before opening a new thread, but ... .

I'd like to have the code insert rows where the dates are missing and assign "?" to them in column C (Value).
Could you please do consider all issues.

1. Datasets are in daily format and the duration is not fixed but sequential ascending. (e.g. 1/1/1949 - 30/12/2010)
2. In a Msg Box, shows the number of inserted rows.
3. Values start from 3rd row and col B&C have different values.
11-22-2014 9-03-17 PM.png
Regards,

Originally Posted by TMS

OK, so I see you have another thread running that will open and process the 1000+ .csv files. So you just need code to fill the date gaps?

http://www.excelforum.com/excel-prog...ple-files.html


Regards, TMS

Yes. Exactly. The files have same structure and some of them have gaps between.
I need to run this code to fill the gaps first, and then run that macro
I attached the sample file. Year 1980 is missing here for example.
Could you please assign "?" in Column C (Value) for inserted rows?

Regards, Mori

Like this?

Please Login or Register  to view this content.

Thank you. Yes. But it shows "Run Time Error: '13'. Type mismatch" in Ln 6.
Do I have to change something in the code?

That error will occur if the data you posted in your sample file are different from those data giving you the error.
I tested that code on your data as you posted and it worked just fine.
It does require your "dates" are numbers (even if formatted differently) otherwise that code will error and the concept of what is "sequential" and hence the meaning of your question becomes rather blurred.

Here's a minor variation of the code that doesn't require data input in colA from A3 downward to be integers. It shouldn't give the same error, but since I don't know the specific structure of your data I consequently can't be sure this code will give the result you want either.

Please Login or Register  to view this content.

Apposite to my original code, you might check that, if you reformat ColumnA as "general", then do all of your "dates" then appear as positive integers from about 28,000 to 40,000?