But I don't know the first thing about code and as helpful as the answer to that post is, I have no idea how to change the code to meet my particular requirements. That is:
I have daily data where the dates sometimes skip days, e.g.:
A1: 1/1/13
A2: 1/3/13. I need to insert a row
A3: 1/4/13
I need to insert a row for 1/2/13 between A1 and A2.
In some cases the data skips two days, e.g.:
A4: 1/5/13
A5: 1/8/13
A6: 1/9/13
In this case I'd need to insert two rows between A4 and A5 for 1/6/13 and 1/7/13, respectively.
Sub InsertRows()
Dim r As Long
Dim mcol As Date
Dim I As Long
' find last used cell in Column A
r = Cells(Rows.Count, "A").End(xlUp).Row
' get value of last used cell in column A
mcol = Cells(r, 1).Value
' insert rows by looping from bottom
For I = r To 2 Step -1
'if current value is not same as the current row being tested
If Cells(i, 1) <> mcol Then
'both values are not same, so insert row
Rows(i + 1).Insert
End If
'change date by one day back
mcol = DateAdd("d", -1, mcol)
Next i
End Sub
6070 users have said thank you to us this month
- Posts
- 4475
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- January 6, 2016
753 -Sub InsertRows() Dim r As Long Dim mcol As Date Dim I As Long ' find last used cell in Column A r = Cells(Rows.Count, "A").End(xlUp).Row ' get value of last used cell in column A mcol = Cells(r, 1).Value ' insert rows by looping from bottom For I = r To 2 Step -1 'if current value is not same as the current row being tested If Cells(i, 1) <> mcol Then 'both values are not same, so insert row Rows(i + 1).Insert End If 'change date by one day back mcol = DateAdd("d", -1, mcol) Next i End Sub- Posts
- 3
- Registration date
- Thursday February 14, 2013
- Status
- Member
- Last seen
- February 15, 2013
2 -