how to list missing all date by blank row between change value cells?

[poem]

i am looking for macro code that isert blank row between data( missing all date) when change value data which each cells are started frist month if not mentied till end of month too

[Kaper]

Try such macro (not most effective but quite strightforward to understand - I hope):

Sub test()
Dim i As Long, lr As Long, start_date As Date, end_date As Date, current_date As Date
Application.ScreenUpdating = False

lr = Cells(Rows.Count, "B").End(xlUp).Row
start_date = DateSerial(2015, 5, 1)
end_date = DateSerial(2015, 6, 1) 'note - this is first day of next month to keep one empty line
current_date = start_date
i = 2
Do
  If Cells(i, "B") = current_date Then
    i = i + 1
    current_date = current_date + 1
  ElseIf Cells(i, "B") <> "" Then
    Rows(i).Insert shift:=xlDown
    i = i + 1
    current_date = current_date + 1
    lr = lr + 1
  ElseIf current_date < end_date Then
    Rows(i).Insert shift:=xlDown
    i = i + 1
    current_date = current_date + 1
    lr = lr + 1
  Else
    i = i + 1
    current_date = start_date
  End If
Loop Until i >= lr
End Sub

[jindon]

Here's my interpretation of your problem.

Sub test()
    Dim i As Long, ii As Long, myAreas As Areas, Sdate As Date, Edate As Date, x As Long
    Application.ScreenUpdating = False
    Set myAreas = Columns("b").SpecialCells(2, 1).Areas
    For i = myAreas.Count To 1 Step -1
        With myAreas(i)
            Sdate = .Cells(1).Value - Day(.Cells(1).Value) + 1
            Edate = DateAdd("m", 1, .Cells(1).Value) - Day(.Cells(1).Value)
            For ii = .Count To 2 Step -1
                If ii = .Count Then
                    x = Edate - .Cells(ii).Value
                    If x > 0 Then .Cells(ii + 1).Resize(x).EntireRow.Insert
                Else
                    x = .Cells(ii).Value - .Cells(ii - 1).Value
                    If x > 1 Then
                        .Cells(ii).Resize(x - 1).EntireRow.Insert
                    End If
                End If
            Next
            x = .Cells(1) - Sdate
            If x > 0 Then .Cells(1).Resize(x).EntireRow.Insert
        End With
    Next
    Application.ScreenUpdating = True
End Sub

[poem]

thank you too much

[jindon]

If that takes care of your original question, select Thread Tools from the menu link above and mark this thread as SOLVED.

[macropod]

Cross-posted at:
http://www.excelguru.ca/forums/showt...ge-value-cells
and:
http://www.msofficeforums.com/excel-...html#post84990