Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Insert Rows and Add Missing Dates

Latest post Tue, Jun 24 2008 11:09 AM by theCruv. 2 replies.
  • Fri, Jun 20 2008 11:51 AM

    • theCruv
    • Top 200 Contributor
    • Joined on Fri, Jun 20 2008
    • Greenville, SC
    • Posts 2
    • Points 26

    Insert Rows and Add Missing Dates

    I have spreadsheets which have dates listed in Column A with other data in subsequent columns.  There may be multiples (even hundreds) of one date listed (see example below).

    5-May-08
    6-May-08
    7-May-08
    7-May-08
    7-May-08
    8-May-08

    There are also missing dates (e.g. 1-May-08 to 4-May-08).  Ultimately, I need every day of a month represented in the list.  So, I need to have rows inserted where a missing date should be, and then I need those dates added in the list.  The dates will be sequential.  No dates/data should be removed.

    Along with this, I need a '0' added in four cells on the newly added row (typically L-O).

    Thanks in advance!

    Filed under: , , ,
    • Post Points: 21
  • Fri, Jun 20 2008 3:58 PM In reply to

    Re: Insert Rows and Add Missing Dates

    Public Sub ProcessData()
    Const TEST_COLUMN As String = "A"    '<=== change to suit
    Dim i As Long
    Dim LastRow As Long
    Dim tmp As Long

        With ActiveSheet
            
            LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
            For i = LastRow To 2 Step -1
            
                If .Cells(i, "A").Value <> .Cells(i + 1, "A").Value And _
                    .Cells(i, "A").Value < .Cells(i + 1, "A").Value - 1 Then
                    
                    tmp = .Cells(i + 1, "A").Value
                    .Rows(i + 1).Resize(tmp - .Cells(i, "A").Value - 1).Insert
                    .Cells(i, "A").AutoFill .Cells(i, "A").Resize(tmp - .Cells(i, "A").Value)
                    .Cells(i + 1, "L").Resize(tmp - .Cells(i, "A").Value - 1, 4).Value = 0
                End If
            Next i
            
        End With
        
    End Sub

    Regards

    Bob

    • Post Points: 21
  • Tue, Jun 24 2008 11:09 AM In reply to

    • theCruv
    • Top 200 Contributor
    • Joined on Fri, Jun 20 2008
    • Greenville, SC
    • Posts 2
    • Points 26

    Re: Insert Rows and Add Missing Dates

    That's absolutely perfect !!  Thanks for the help !!!!!

    • Post Points: 5
Page 1 of 1 (3 items) | RSS
Copyright Excel User Group and the relevant contributors, 2008. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.