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

[malycom]

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

 

[Don Guillett]

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]

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

 

[malycom]

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

 

[malycom]

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]

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

 

[Don Guillett]

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

 

[malycom]

Hi Don

Many thanks for your help.

I have emailed you a spreadsheet as per your suggestions.

Regards

Malcolm

 

[malycom]

Hi Don

Many thanks for your help.

I have emailed you a spreadsheet as per your suggestions.

Regards

Malcolm