Insert Missing Times that are in Sequential Order

  • I found this in the thread https://www.ozgrid.com/forum/f…quential-dates-times-list and was trying to modify it to fit my needs. This is what I have so far; I am trying to find those times that are missing from the filtered list but it is just coping all the times. It works for non filter times perfectly but not when I apply a filter to the data. Any help will much appreciated.


    Sub rowinsert()

    Dim ThisTime As Double
    Dim NextTime As Double
    Dim cel As Range
    Dim rng As Range
    Dim LastRow As Long


    'Select sheet
    Sheets("Sheet5").Activate

    'Get last row of data
    LastRow = Range("B" & Rows.Count).End(xlUp).Row

    'Where to look
    Set rng = Range("C2:C" & LastRow)

    'Chek all
    For Each cel In Intersect(rng, ActiveWindow.VisibleRange).SpecialCells(xlCellTypeVisible)
    'Check if done
    If cel.Offset(1, 0) = vbNullString Then GoTo endo
    'Add 15 mins to cell value
    ThisTime = Round((cel.Offset(0, 0) + TimeValue("00:15")) * 24 * 4) / 4 / 24
    'Get next cel time
    NextTime = Round(cel.Offset(1, 0) * 24 * 4) / 4 / 24

    'Check if toime is + 15
    If ThisTime <> NextTime Then

    'No. Insert a row
    cel.Offset(1, 0).EntireRow.Insert shift:=xlDown
    'Put next req'd time
    cel.Offset(1, 0) = ThisTime
    'Put 'N/A'
    cel.Offset(1, 1) = 0
    ' Range(cel.Offset(1, 1), cel.Offset(1, 7)) = "N/A"
    End If
    Next

    endo:


    'Cleanup
    Set cel = Nothing
    Set rng = Nothing


    End Sub

  • Hello and Welcome to the Forum :smile:


    To make everything easier ... why don't you attach your sample file ...:wink:


    Please explain the difference between " filtered times " ...and ..." non filtered times " ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Example of Data, between 14:15 and 15:30, I am wanting to add the next .15 increments of time (14:30, 14:45, 15:00, 15:15). For filtered times, I mean what is selected by the auto filter in Excel [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 64, align: right"]12:30[/TD]
    [TD="width: 64, align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="align: right"]12:45[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="align: right"]13:00[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="align: right"]13:15[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="align: right"]13:30[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="align: right"]13:45[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="align: right"]14:00[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="align: right"]14:15[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="align: right"]15:30[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="align: right"]15:45[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="align: right"]16:00[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="align: right"]16:15[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="align: right"]16:30[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="align: right"]16:45[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="align: right"]17:00[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="align: right"]17:15[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="align: right"]17:30[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="align: right"]17:45[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [/TABLE]

  • Sorry ... still cannot understand the difference between " filtered times " ...and ..." non filtered times " ...


    And ...


    Quote

    why don't you attach your sample file ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)