Results 1 to 2 of 2

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 .
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,811
    Articles
    0
    Excel Version
    365
    poem, you have cross posted this at http://www.msofficeforums.com/excel-...blank-row.html

    Cross posting is OK if you tell people everywhere you have cross posted to, at all the sites involved.
    Please have a read of http://www.excelguru.ca/content.php?184
    Ultimately it's to your benefit.

    This time:
    Code:
    Sub blah()
    For Each are In ActiveSheet.UsedRange.Offset(1).SpecialCells(xlCellTypeConstants, 23).Areas
      diff = CLng(Application.WorksheetFunction.EoMonth(are.Rows(are.Rows.Count).Cells(2).Value, 0) - are.Rows(are.Rows.Count).Cells(2).Value)
      If diff > 0 Then
        are.Rows(are.Rows.Count).Offset(1).Resize(diff).Insert Shift:=xlDown
        are.Rows(are.Rows.Count).Resize(, 2).AutoFill Destination:=are.Rows(are.Rows.Count).Resize(diff + 1, 2)
      End If
      For rw = are.Rows.Count To 1 Step -1
        Select Case rw
          Case 1
            diff = Day(are.Rows(rw).Cells(2).Value)
            If diff > 1 Then
            are.Rows(rw).Resize(diff - 1).Insert Shift:=xlDown
            are.Rows(rw).Resize(, 2).AutoFill Destination:=are.Rows(rw).Offset(1 - diff).Resize(diff, 2), Type:=xlFillDefault
            End If
          Case Else
            diff = are.Rows(rw).Cells(2).Value - are.Rows(rw - 1).Cells(2).Value
            If diff > 1 Then
              are.Rows(rw).Resize(diff - 1).Insert Shift:=xlDown
              are.Rows(rw - 1).Resize(, 2).AutoFill Destination:=are.Rows(rw - 1).Resize(diff, 2), Type:=xlFillDefault
            End If
        End Select
      Next rw
    Next are
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •