Insert Row using Macro, Group By Date

[JRW]

I am pulling data from a sql database and would like to write a macro
to insert a date after each month. Example

Before:
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

 

[Tom Ogilvy]

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.

 

[Dave Peterson]

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)

 

[Bob Phillips]

What a left-field thought<vbg>.

Bob

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)