Macro-separate different dates with two grey rows

L

lunker55

Excel 2000: In column B, there are about 200 rows with dates. The sheet is
sorted by date. The dates have a range of about 3 weeks.
There may be between 1 and 35 rows with the same date. Is there a way to
automatically insert 2 grey rows between the different dates?
Example: I enter 21april in row 1, then 22april in row 2, and 2 grey rows
separate automatically.

Joe
 
F

Frank Kabel

Hi Joe
try the following macro
Sub insert_rows()
Dim lastrow As Long
Dim row_index As Long

lastrow = ActiveSheet.Cells(Rows.count, "A").End(xlUp).row
For row_index = lastrow - 1 To 1 Step -1
If Cells(row_index, "A").Value <> Cells(row_index + 1, "A"). _
Value Then
Cells(row_index + 1, "A").resize(2,1).EntireRow. _
Insert (xlShiftDown)
Cells(row_index + 1, "A").resize(2,1).EntireRow. _
interior.colorindex=15
End If
Next
End Sub
 
L

lunker55

Thanks for the help Frank.
I can't get it to work though.
"A" is referring to column A?
It doesn't look like I need to change anything if my dates are in culomn A.

Joe
 
L

lunker55

Thank you very much Frank. I got it working.
Had to assign it to a macro button.

Joe
 
L

lunker55

Frank, is there a way to start in row 3?
Row 1 and 2 are header rows and I don't want spaces between them.

Joe
 
F

Frank Kabel

Hi
change the line
For row_index = lastrow - 1 To 1 Step -1

to
For row_index = lastrow - 1 To 3 Step -1
 
Top