Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add rows between months
Hello all.
I have 10 years worth of data that I would like to separate by month. As of now there is no separation between, for example, January and February of 2000. An example is this: 01/04/2000 01/10/2000 01/26/2000 02/05/2000 02/17/2000 I would like a macro that would automatically insert three rows between each month. This is tricky because there are a differing number of entries for each month. January might have three, while February has 2, etc. I would like this to be applied to the entire 10 year period. Any help is appreciated. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add rows between months
Josh,
Does Excel's dataSubTotal achieve your goal ? You may need to add a helper column, Month(<DateColumn). NickHK "Josh" wrote in message ups.com... Hello all. I have 10 years worth of data that I would like to separate by month. As of now there is no separation between, for example, January and February of 2000. An example is this: 01/04/2000 01/10/2000 01/26/2000 02/05/2000 02/17/2000 I would like a macro that would automatically insert three rows between each month. This is tricky because there are a differing number of entries for each month. January might have three, while February has 2, etc. I would like this to be applied to the entire 10 year period. Any help is appreciated. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add rows between months
If nick's Idea doesn't work then try this:
Sub SplitMonth() Dim Cnt As Double Dim CurrCell As Range Dim PrevCell As Range Cnt = 1 'Change to your starting row Set PrevCell = Range("A1") 'Change to your starting cell Do Set CurrCell = PrevCell.Offset(1,0) If Evaluate(Month(PrevCell) < Month(CurrCell)) Then Range(CurrCell, CurrCell.Offset(2, 0)).EntireRow.Insert xlDown Cnt = Cnt + 3 Else Cnt = Cnt + 1 End If Set PrevCell = CurrCell Loop While CurrCell < "" End Sub HTH Die_Another_Day NickHK wrote: Josh, Does Excel's dataSubTotal achieve your goal ? You may need to add a helper column, Month(<DateColumn). NickHK "Josh" wrote in message ups.com... Hello all. I have 10 years worth of data that I would like to separate by month. As of now there is no separation between, for example, January and February of 2000. An example is this: 01/04/2000 01/10/2000 01/26/2000 02/05/2000 02/17/2000 I would like a macro that would automatically insert three rows between each month. This is tricky because there are a differing number of entries for each month. January might have three, while February has 2, etc. I would like this to be applied to the entire 10 year period. Any help is appreciated. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3 months prior and 3 months post a date | Excel Worksheet Functions | |||
auto fill rows with months based on cell value | Excel Discussion (Misc queries) | |||
Running total of last 6 months in rows | Excel Discussion (Misc queries) | |||
Adding months to dates should account for 28-30-31 day months | Excel Worksheet Functions | |||
Dates to months and calculating values for their months | Excel Programming |