Is this even possible? VBA question about adding rows for blank data...

[nkemp15]

I have a report that I run that will pull data for how many calls a team received for every 15 minute interval of the day. The issue I'm having with this report, is if there were no calls received, no data is put on the report. For example, it will give information for 1:00 1:15 1:30 2:00 & 2:15. In my example, no calls were received between 1:45 and 2:00, so 1:45 is completely missing from the report.

Is it possible to write a VBA script that will look through the times, and add in the missing rows with the blank times? I don't need any other data (since it would all be zeros anyways) I just need the rows to be added so management can visualize the times with zero calls.

 

[Momentman]

From 6 in the morning to 12 at night?. Wow! Now that's something

 

[nkemp15]

Yeah, the day on the screenshot was a Saturday so there weren't that many calls. But my department covers 6 time zones and our report shows everything in +5 time.

 

[Momentman]

Yeah, the day on the screenshot was a Saturday so there weren't that many calls. But my department covers 6 time zones and our report shows everything in +5 time.

Not sure how the time zone impacts the calculations yet.

Anyways, apologies if am asking too many questions, we need to be on the same page to get the macro working flawlessly

Between Rows 31 and 32, 1.e 6pm on 2/1/2014 till 9am on 2/2/2014...how do we fill it? we include rows to cover from 6PM till 12Am and another to start 9AM to cover till 9AM on the next day?

Is that right?

 

[nkemp15]

Yeah. That would be ideal. But if it just did all times that wouldn't matter. I can delete 6 hours.

 

[Momentman]

Funny enough a similar code to the one i posted before seems to work for me

Sub addtimes()
    Dim I As Integer, j As Integer
    For I = Range("A" & Rows.Count).End(xlUp).Row To 3 Step -1
        a = 1 * Format(48 * (Range("A" & I) - Range("A" & I - 1)), "#")
        If a <> 1 Then
            For j = 1 To a - 1
                Rows(I).Resize(1).Insert
                Range("A" & I).Value = Range("A" & I + 1).Value - 1 / 48
                Range("B" & I).Value = 0
            Next j
        End If
    Next I
    
End Sub

If you get any errors, let me know, you might need to send a sample excel sheet with the exact structure o data even though fictitious data

 

[mirabeau]

I have a report that I run that will pull data for how many calls a team received for every 15 minute interval of the day. The issue I'm having with this report, is if there were no calls received, no data is put on the report. For example, it will give information for 1:00 1:15 1:30 2:00 & 2:15. In my example, no calls were received between 1:45 and 2:00, so 1:45 is completely missing from the report.

Is it possible to write a VBA script that will look through the times, and add in the missing rows with the blank times? I don't need any other data (since it would all be zeros anyways) I just need the rows to be added so management can visualize the times with zero calls.

There's what seems to be a similar problem in this recent thread

http://www.mrexcel.com/forum/excel-questions/766334-data-missing-seris-dates-anybody-can-help.html

Post #10 there has a code resolving that particular problem.

Maybe your current problem is sufficiently similar.