Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text to Rows and then Insert Blank Rows | Excel Discussion (Misc queries) | |||
Insert Blank Rows | Excel Discussion (Misc queries) | |||
How do i insert blank rows between data that is thousands of rows | Excel Discussion (Misc queries) | |||
Insert A blank Row Everytime the date changes in column B | Excel Discussion (Misc queries) | |||
How do I insert blank rows between rows in completed worksheet? | Excel Discussion (Misc queries) |