Insert Row using Macro, Group By Date

  • Thread starter Thread starter JRW
  • Start date Start date
J

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
 
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.
 
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)
 
What a left-field thought<vbg>.

Bob

Dave Peterson said:
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)
Click to expand...
 
Back
Top