Insert 3 blank rows wherever the date changes in column????
 

Hi

I have a worksheet of various transactions sorted in date order. There may
be 1 row of data for a specific date if there was only one transaction and
another date may have 40 rows of data as there were 40 transactions.

This is for a complete month so approx 30 days

What I would like to do is to run a macro or something that will search down
the date column and insert 3 blank rows at every row where the date changes,
ie 01/04/2009 changes to 02/04/2009 and then again when that changes to
03/04/2009 and do this for the whole month on the worksheet.

I would be grateful if you can give a detailed explanation as to how to do
this if it is possible as I have absolutely no idea.

Thanks in advance

Malcolm

Insert 3 blank rows wherever the date changes in column????
 

Should do it. Change mc to suit

Sub insertrowsatdatechnage()
mc = "a"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If Cells(i - 1, mc) < Cells(i, mc) Then Rows(i).Resize(3).Insert
Next
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"malycom" wrote in message
...
Hi

I have a worksheet of various transactions sorted in date order. There
may
be 1 row of data for a specific date if there was only one transaction and
another date may have 40 rows of data as there were 40 transactions.

This is for a complete month so approx 30 days

What I would like to do is to run a macro or something that will search
down
the date column and insert 3 blank rows at every row where the date
changes,
ie 01/04/2009 changes to 02/04/2009 and then again when that changes to
03/04/2009 and do this for the whole month on the worksheet.

I would be grateful if you can give a detailed explanation as to how to do
this if it is possible as I have absolutely no idea.

Thanks in advance

Malcolm

Insert 3 blank rows wherever the date changes in column????
 

Don, you are a hero.

That worked a treat.

I don't suppose you would be able to help in the last bit now?

I now have 20 blocks of data with each bit being a set of transactions on a
certain date.

To the left of the date colum are 2 columns that I would like to auto sum,
one being units of hours and the other being charge rates.

This needs to be done for each date block you kindly helped with and the
columns in each block for auto summing are C & D.

Thanks very much for your help so far, regardless as to whether you can
assist with this bit or not.

Malcolm



"Don Guillett" wrote:

Should do it. Change mc to suit

Sub insertrowsatdatechnage()
mc = "a"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If Cells(i - 1, mc) < Cells(i, mc) Then Rows(i).Resize(3).Insert
Next
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"malycom" wrote in message
...
Hi

I have a worksheet of various transactions sorted in date order. There
may
be 1 row of data for a specific date if there was only one transaction and
another date may have 40 rows of data as there were 40 transactions.

This is for a complete month so approx 30 days

What I would like to do is to run a macro or something that will search
down
the date column and insert 3 blank rows at every row where the date
changes,
ie 01/04/2009 changes to 02/04/2009 and then again when that changes to
03/04/2009 and do this for the whole month on the worksheet.

I would be grateful if you can give a detailed explanation as to how to do
this if it is possible as I have absolutely no idea.

Thanks in advance

Malcolm

Insert 3 blank rows wherever the date changes in column????
 

Instead of trying to reconstruct, if desired, send your file to me along
with this msg and before/after example.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"malycom" wrote in message
...
Don, you are a hero.

That worked a treat.

I don't suppose you would be able to help in the last bit now?

I now have 20 blocks of data with each bit being a set of transactions on
a
certain date.

To the left of the date colum are 2 columns that I would like to auto sum,
one being units of hours and the other being charge rates.

This needs to be done for each date block you kindly helped with and the
columns in each block for auto summing are C & D.

Thanks very much for your help so far, regardless as to whether you can
assist with this bit or not.

Malcolm



"Don Guillett" wrote:

Should do it. Change mc to suit

Sub insertrowsatdatechnage()
mc = "a"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If Cells(i - 1, mc) < Cells(i, mc) Then Rows(i).Resize(3).Insert
Next
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"malycom" wrote in message
...
Hi

I have a worksheet of various transactions sorted in date order. There
may
be 1 row of data for a specific date if there was only one transaction
and
another date may have 40 rows of data as there were 40 transactions.

This is for a complete month so approx 30 days

What I would like to do is to run a macro or something that will search
down
the date column and insert 3 blank rows at every row where the date
changes,
ie 01/04/2009 changes to 02/04/2009 and then again when that changes to
03/04/2009 and do this for the whole month on the worksheet.

I would be grateful if you can give a detailed explanation as to how to
do
this if it is possible as I have absolutely no idea.

Thanks in advance

Malcolm

Insert 3 blank rows wherever the date changes in column????
 

Hi Don

Many thanks for your help.

I have emailed you a spreadsheet as per your suggestions.

Regards

Malcolm

"Don Guillett" wrote:

Instead of trying to reconstruct, if desired, send your file to me along
with this msg and before/after example.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"malycom" wrote in message
...
Don, you are a hero.

That worked a treat.

I don't suppose you would be able to help in the last bit now?

I now have 20 blocks of data with each bit being a set of transactions on
a
certain date.

To the left of the date colum are 2 columns that I would like to auto sum,
one being units of hours and the other being charge rates.

This needs to be done for each date block you kindly helped with and the
columns in each block for auto summing are C & D.

Thanks very much for your help so far, regardless as to whether you can
assist with this bit or not.

Malcolm



"Don Guillett" wrote:

Should do it. Change mc to suit

Sub insertrowsatdatechnage()
mc = "a"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If Cells(i - 1, mc) < Cells(i, mc) Then Rows(i).Resize(3).Insert
Next
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"malycom" wrote in message
...
Hi

I have a worksheet of various transactions sorted in date order. There
may
be 1 row of data for a specific date if there was only one transaction
and
another date may have 40 rows of data as there were 40 transactions.

This is for a complete month so approx 30 days

What I would like to do is to run a macro or something that will search
down
the date column and insert 3 blank rows at every row where the date
changes,
ie 01/04/2009 changes to 02/04/2009 and then again when that changes to
03/04/2009 and do this for the whole month on the worksheet.

I would be grateful if you can give a detailed explanation as to how to
do
this if it is possible as I have absolutely no idea.

Thanks in advance

Malcolm