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

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

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

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