Inserting rows for Missing Date/ Time data (Excel 2007)

[mzalikhan]

Hi everybody

I have got an hourly data for 10 years. The problem is that some of the data is missing. I need to insert new rows for the missing hours and put the "date hour" in the respective column. For example what i have is column A as
A
1/1/2000 00:30 AM
1/1/2000 01:30 AM
1/1/2000 02:30 AM
1/1/2000 04:30 AM
1/1/2000 05:30 AM

Please note that in 4th row, it should be 1/1/2000 03:30, which is missing. So what i need to have is

1/1/2000 00:30 AM
1/1/2000 01:30 AM
1/1/2000 02:30 AM
1/1/2000 03:30 AM
1/1/2000 04:30 AM
1/1/2000 05:30 AM

By the way data starts from 2000 and ends at 2010.

Peace
Muhammad Zeeshan

 

[VoG]

Try this with a copy of your sheet

Sub Missing()
Dim LR As Long, i As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    If CLng((Range("A" & i).Value - Range("A" & i - 1).Value) * 24) <> 1 Then
        Rows(i).Insert
        Range("A" & i).Value = Range("A" & i - 1).Value + 1 / 24
    End If
Next i
Application.ScreenUpdating = True
End Sub

 

[mzalikhan]

Hi Vog

Thanks for the code. I just tried it its not working. On clicking debug option, it gives a hint of following problem.

If CLng((Range("A" & i).Value - Range("A" & i - 1).Value) * 24)= <Type Mismatch>

I don't understand much about this thing. Can you please see it again?

Peace

 

[mzalikhan]

By the way it is inserting some empty cells.... but... after every 23:30, it inserts 00:30 then again 23:30 and 00:30 and moves forward normally.