Insert new rows when date changes 1

[abdulkarym]

Hello everyone,
I found some useful codes here about inserting new rows as cell value changes. Codes such as:

Sub insert_spaces()

Dim last_row As Long
Dim cur_row As String
Dim prev_row As String
Dim f As Integer

last_row = Cells(65536, 1).End(xlUp).Row
For f = last_row To 2 Step -1
cur_row = Cells(f, 1)
prev_row = Cells(f - 1, 1)
If cur_row <> prev_row And prev_row <> "" Then
Rows(f).Select
Selection.Insert Shift:=xlDown
last_row = last_row + 1
End If
Next f

End Sub

the above code runs below:-

1234 1234
1234 1234
5546
5546 5546
5465 5546
7895 >>> Becomes
7895 5465

7895
7895

But my own case is a bit complex, deals with date and maybe calculations. Lets say my first row starts with:
01/01/2005 08:40:00
having 10minutes interval, and ends with:
01/01/2005 16:50:00

i.e something as such:
01/01/2005 08:40:00
01/01/2005 08:50:00
01/01/2005 09:00:00
01/01/2005 09:10:00
01/01/2005 09:20:00
01/01/2005 09:30:00
01/01/2005 09:40:00
01/01/2005 09:50:00
01/01/2005 10:00:00
01/01/2005 10:10:00
01/01/2005 10:20:00
01/01/2005 10:30:00
01/01/2005 10:40:00
"
"
"
way down to 31st.

I have to check if it starts at:
01/01/2005 00:10:00

If not, then I need to insert new rows at 10minutes interval starting from:
01/01/2005 00:10:00 until 01/01/2005 08:40:00

And then I have to make another check if it ends at:
01/01/2005 00:00:00

If not then I have to insert new 10minutes interval rows starting the last date in the dataset (i.e 01/01/2005 16:50:00) until 01/01/2005 00:00:00

And then I go to next day (i.e 01/02/2005) and do same and then through out the whole year.

I hope it is not that complex to the gurus around. Looking forward for your help.

Thanks
Abdul

Here is some set of data, might be useful for testing.
01/01/2005 08:40:00
01/01/2005 08:50:00
01/01/2005 09:00:00
01/01/2005 09:10:00
01/01/2005 09:20:00
01/01/2005 09:30:00
01/01/2005 09:40:00
01/01/2005 09:50:00
01/01/2005 10:00:00
01/01/2005 10:10:00
01/01/2005 10:20:00
01/01/2005 10:30:00
01/01/2005 10:40:00
01/01/2005 10:50:00
01/01/2005 11:00:00
01/01/2005 11:10:00
01/01/2005 11:20:00
01/01/2005 11:30:00
01/01/2005 11:40:00
01/01/2005 11:50:00
01/01/2005 12:00:00
01/01/2005 12:10:00
01/01/2005 12:20:00
01/01/2005 12:30:00
01/01/2005 12:40:00
01/01/2005 12:50:00
01/01/2005 13:00:00
01/01/2005 13:10:00
01/01/2005 13:20:00
01/01/2005 13:30:00
01/01/2005 13:40:00
01/01/2005 13:50:00
01/01/2005 14:00:00
01/01/2005 14:10:00
01/01/2005 14:20:00
01/01/2005 14:30:00
01/01/2005 14:40:00
01/01/2005 14:50:00
01/01/2005 15:00:00
01/01/2005 15:10:00
01/01/2005 15:20:00
01/01/2005 15:30:00
01/01/2005 15:40:00
01/01/2005 15:50:00
02/01/2005 08:30:00
02/01/2005 08:40:00
02/01/2005 08:50:00
02/01/2005 09:00:00
02/01/2005 09:10:00
02/01/2005 09:20:00
02/01/2005 09:30:00
02/01/2005 09:40:00
02/01/2005 09:50:00
02/01/2005 10:00:00
02/01/2005 10:10:00
02/01/2005 10:20:00
02/01/2005 10:30:00
02/01/2005 10:40:00
02/01/2005 10:50:00
02/01/2005 11:00:00
02/01/2005 11:10:00
02/01/2005 11:20:00
02/01/2005 11:30:00
02/01/2005 11:40:00
02/01/2005 11:50:00
02/01/2005 12:00:00
02/01/2005 12:10:00
02/01/2005 12:20:00
02/01/2005 12:30:00
02/01/2005 12:40:00
02/01/2005 12:50:00
02/01/2005 13:00:00
02/01/2005 13:10:00
02/01/2005 13:20:00
02/01/2005 13:30:00
02/01/2005 13:40:00
02/01/2005 13:50:00
02/01/2005 14:00:00
02/01/2005 14:10:00
02/01/2005 14:20:00
02/01/2005 14:30:00
02/01/2005 14:40:00
02/01/2005 14:50:00
02/01/2005 15:00:00
02/01/2005 15:10:00
02/01/2005 15:20:00
02/01/2005 15:30:00
02/01/2005 15:40:00
02/01/2005 15:50:00
02/01/2005 16:00:00
02/01/2005 16:10:00
03/01/2005 08:20:00
03/01/2005 08:30:00
03/01/2005 08:40:00
03/01/2005 08:50:00
03/01/2005 09:00:00
03/01/2005 09:10:00
03/01/2005 09:20:00
03/01/2005 09:30:00
03/01/2005 09:40:00
03/01/2005 09:50:00
03/01/2005 10:00:00
03/01/2005 10:10:00
03/01/2005 10:20:00
03/01/2005 10:30:00
03/01/2005 10:40:00
03/01/2005 10:50:00
03/01/2005 11:00:00
03/01/2005 11:10:00
03/01/2005 11:20:00
03/01/2005 11:30:00
03/01/2005 11:40:00
03/01/2005 11:50:00
03/01/2005 12:00:00
03/01/2005 12:10:00
03/01/2005 12:20:00
03/01/2005 12:30:00
03/01/2005 12:40:00
03/01/2005 12:50:00
03/01/2005 13:00:00
03/01/2005 13:10:00
03/01/2005 13:20:00
03/01/2005 13:30:00
03/01/2005 13:40:00
03/01/2005 13:50:00
03/01/2005 14:00:00
03/01/2005 14:10:00
03/01/2005 14:20:00
03/01/2005 14:30:00
03/01/2005 14:40:00
03/01/2005 14:50:00
03/01/2005 15:00:00
03/01/2005 15:10:00
03/01/2005 15:20:00
03/01/2005 15:30:00
03/01/2005 15:40:00
03/01/2005 15:50:00
03/01/2005 16:00:00

 

[SkipVought]

Hi,

It a horrible idea to intersperse empty rows within a table of contiguous data. It comes from uninformed users who want a sheet to look pretty while destroying the functionality of the data and rendering the plethora of data reporting and analysis tools available in Excel, virtually worthless.

Consider employing the Row Height property or other range formatting features. Take a look at the Subtotal Feature in Data/Subtotal or the PivotTable Wizard.

In fact the PivotTable may be the ticket. You can use the Group and Outline on the Date column to group in a similar manner. The PT Wizard produces a REPORT from your source data, which should remain in tact.


Skip,

_[red][/red]

 

[sallylou]

I have just tried your macro to insert new rows as cell value changes.

I have column A, B, and C all of which I want to add a new row to at a change of value.

What do I have to do to the macro to make it work in columns B & C as well as A?

Thanks - sorry new to Macro's.

Sally

 

[SkipVought]

Selection.entirerow.Insert Shift:=xlDown

but I repeat...

its usually NOT a good (best practices) idea.

Skip,

_[red][/red]

 

[sallylou]

I want to do it in A, B and C but I have other data in E through to K.

I don't have formulas in this spreadsheet - it's a price list.

The items are coded as to where they belong by code in A, B & C. I want to add blank lines with each change, copy the code up into the blank space, then using vlookup bring the headings across from another spreadsheet.

Not sure if vlookups are the best way to do it but I can make them work - I am just brand new to macros.

Thanks for your reply

 

[missinglinq]

I don't believe I've ever seen the poster of a request for help awarded the Order of the Pink Star before!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[SkipVought]

sallylou,

Chances are you are doing alot of unnecessary stuff in order to achieve your result.


Take a look at the powerful capabilities of these data analysis and reporting tools

AutoFilter
Advanced Filter
PivotTable Wizard
Subtotal

With these you MUST keep your source data table in tact.

No copy 'n' paste required.

No Insert/Delete required.

Skip,

_[red][/red]