Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Row using Macro, Group By Date
I am pulling data from a sql database and would like to write a macro
to insert a date after each month. Example Befo 4/5/03 4/6/03 5/2/03 5/12/03 5/23/03 6/1/03 After 4/5/03 4/6/03 5/2/03 5/12/03 5/23/03 6/1/03 How would go about coding this? Thanks in Advance! Jason |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Row using Macro, Group By Date
Sub aaTester9()
Set rng = ActiveCell.Offset(1, 0) Do While Not Application.CountA(rng.Resize(2, 1)) = 0 If Month(rng) < Month(rng.Offset(-1, 0)) Then rng.EntireRow.Insert End If Set rng = rng.Offset(1, 0) Loop End Sub select the cell at the top of the column. -- Regards, Tom Ogilvy "JRW" wrote in message om... I am pulling data from a sql database and would like to write a macro to insert a date after each month. Example Befo 4/5/03 4/6/03 5/2/03 5/12/03 5/23/03 6/1/03 After 4/5/03 4/6/03 5/2/03 5/12/03 5/23/03 6/1/03 How would go about coding this? Thanks in Advance! Jason |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Row using Macro, Group By Date
Before you add that extra row, think about not doing it. Maybe you could just
double the rowheight of the next row. It'll give the appearance of double spacing, but it might make other stuff easier (sorting/pivottables/charts). Option Explicit Sub testme01() Dim iRow As Long Dim firstRow As Long Dim lastRow As Long Dim wks As Worksheet Set wks = Worksheets("sheet1") With wks firstRow = 2 lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = lastRow To firstRow Step -1 If Month(.Cells(iRow, "A").Value) _ < Month(.Cells(iRow - 1, "A").Value) _ Or Year(.Cells(iRow, "A").Value) _ < Year(.Cells(iRow - 1, "A").Value) Then '.Rows(iRow).Insert .Rows(iRow).RowHeight = 2 * .Rows(iRow).RowHeight End If Next iRow End With End Sub (And I added a check to see if the year changed, but the month stayed the same) JRW wrote: I am pulling data from a sql database and would like to write a macro to insert a date after each month. Example Befo 4/5/03 4/6/03 5/2/03 5/12/03 5/23/03 6/1/03 After 4/5/03 4/6/03 5/2/03 5/12/03 5/23/03 6/1/03 How would go about coding this? Thanks in Advance! Jason -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Row using Macro, Group By Date
What a left-field thought<vbg.
Bob "Dave Peterson" wrote in message ... Before you add that extra row, think about not doing it. Maybe you could just double the rowheight of the next row. It'll give the appearance of double spacing, but it might make other stuff easier (sorting/pivottables/charts). Option Explicit Sub testme01() Dim iRow As Long Dim firstRow As Long Dim lastRow As Long Dim wks As Worksheet Set wks = Worksheets("sheet1") With wks firstRow = 2 lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = lastRow To firstRow Step -1 If Month(.Cells(iRow, "A").Value) _ < Month(.Cells(iRow - 1, "A").Value) _ Or Year(.Cells(iRow, "A").Value) _ < Year(.Cells(iRow - 1, "A").Value) Then '.Rows(iRow).Insert .Rows(iRow).RowHeight = 2 * .Rows(iRow).RowHeight End If Next iRow End With End Sub (And I added a check to see if the year changed, but the month stayed the same) JRW wrote: I am pulling data from a sql database and would like to write a macro to insert a date after each month. Example Befo 4/5/03 4/6/03 5/2/03 5/12/03 5/23/03 6/1/03 After 4/5/03 4/6/03 5/2/03 5/12/03 5/23/03 6/1/03 How would go about coding this? Thanks in Advance! Jason -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert blank row macro after a break in a group | Excel Discussion (Misc queries) | |||
Macro to Insert Current Date into cell - Macro to "Save As" | Excel Worksheet Functions | |||
How can I insert a date with an icon (calendar) insert | Excel Discussion (Misc queries) | |||
Insert date in macro | New Users to Excel | |||
How do I insert the date using a macro | Excel Discussion (Misc queries) |