insert row when required

[acwarwick]

I please need your help in programming a macro for inserting a row between certain rows.

i have a document with about 33000 dates and times in the B column
that are formatted like thisthe time and the date are in the same column)
27.07.2007 21:45
27.07.2007 21:46
27.07.2007 21:50
27.07.2007 21:51

Now what i need the macro to do is insert a a blank row for every missing time and then write the missing times in...so that it looks like this:

27.07.2007 21:45
27.07.2007 21:46
27.07.2007 21:47
27.07.2007 21:48
27.07.2007 21:49
27.07.2007 21:50
27.07.2007 21:51

Thank-you for your help

[oldchippy]

Are you sure you need a macro? If you put your first date/time in C1 and your second date/time in C2 then auto-fill down to C33000, you finish up with 19/08/2007 19:44:00

[acwarwick]

sorry I explained it badly,

this column with the dates is just part of some data I copied into excel
but for me to use the data i need a row with every date and time possible without any missing.
this is why i need it to enter rows with the missing dates and times

[oldchippy]

Can you still do a column like I suggested, then do a vlookup to bring in the data against the appropiate time/date?

[acwarwick]

thank-you that should work

but how do you enter only dates from monday til friday between 8am and 22pm??

[oldchippy]

Using your times in 33,000 rows in column B, I have used a few helper columns to arrive at Monday - Friday 08:00-22:00 times, the file was to large to post here so here is the start in the attached.

I will explain what you need to do now to complete it from here.

Select cells B20:B21 and auto-fill down the column to row 33,000.

Select C21 and double click on the black square in the bottom right hand corner of the cell. These will auto-fill down to row 33,000. A quick check to see that all the cells are full – double click on the bottom cell border of C21. You should now be on row 33,000.

Select D21 and do exactly the same, also E21, F21 and G21.

You will notice some of the cells are blank, but they are meant to be – don’t worry.

Now with all this done, select column G, copy, go to sheet2, select A1, right click, Paste Special > Values. To format this column the same as sheet1 column B, select sheet1 cell B1, click the Paintbrush icon and click on sheet2 column A. They should now be in the right format – widen column to see if necessary.

With column A selected, click the A-Z icon to put them into order.

You should now have all the workdays Monday-Friday 08:00-22:00 in order.

There may be a quicker way, but try it.