Insert Row Above and Calculate Difference Between Dates
 

I have a full worksheet of about 5,000 rows. I am looking for help so I can
insert a row above each row with contents starting in Row 2. Minus the date
in Column C for each row with contents from todays date and put the
difference in Column B the empty row above. Here is what I have now. If you
can help me, thank you.

Sub Insertrowcalculate()
Range("2:2,4:4,6:6,8:8,10:10").Select
Selection.Insert Shift:=xlDown
Range("4:4,7:7,10:10,13:13").Select
Selection.Insert Shift:=xlDown
End Sub

Insert Row Above and Calculate Difference Between Dates
 

With data as below

Col A Col B Col C
Field 1 Field 2 Field 3
a a 8/11/2009
b b 8/12/2009
c c 8/13/2009
d d 8/14/2009
e e 8/15/2009
f f 8/16/2009
g g 8/17/2009
h h 8/18/2009
i i 8/19/2009

try the below macro..

Sub MyMacro()
Dim lngRow As Long, blnInsert As Boolean
lngRow = 2
Do While lngRow <= Cells(Rows.Count, "C").End(xlUp).Row
blnInsert = Not blnInsert
If blnInsert Then
Rows(lngRow).Insert
Range("B" & lngRow) = Date - Range("C" & lngRow + 1)
End If
lngRow = lngRow + 1
Loop
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Jazz" wrote:

I have a full worksheet of about 5,000 rows. I am looking for help so I can
insert a row above each row with contents starting in Row 2. Minus the date
in Column C for each row with contents from todays date and put the
difference in Column B the empty row above. Here is what I have now. If you
can help me, thank you.

Sub Insertrowcalculate()
Range("2:2,4:4,6:6,8:8,10:10").Select
Selection.Insert Shift:=xlDown
Range("4:4,7:7,10:10,13:13").Select
Selection.Insert Shift:=xlDown
End Sub

Insert Row Above and Calculate Difference Between Dates
 

O Snap. This is the b-diggity man! Thank you for your help.

"Jacob Skaria" wrote:

With data as below

Col A Col B Col C
Field 1 Field 2 Field 3
a a 8/11/2009
b b 8/12/2009
c c 8/13/2009
d d 8/14/2009
e e 8/15/2009
f f 8/16/2009
g g 8/17/2009
h h 8/18/2009
i i 8/19/2009

try the below macro..

Sub MyMacro()
Dim lngRow As Long, blnInsert As Boolean
lngRow = 2
Do While lngRow <= Cells(Rows.Count, "C").End(xlUp).Row
blnInsert = Not blnInsert
If blnInsert Then
Rows(lngRow).Insert
Range("B" & lngRow) = Date - Range("C" & lngRow + 1)
End If
lngRow = lngRow + 1
Loop
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Jazz" wrote:

I have a full worksheet of about 5,000 rows. I am looking for help so I can
insert a row above each row with contents starting in Row 2. Minus the date
in Column C for each row with contents from todays date and put the
difference in Column B the empty row above. Here is what I have now. If you
can help me, thank you.

Sub Insertrowcalculate()
Range("2:2,4:4,6:6,8:8,10:10").Select
Selection.Insert Shift:=xlDown
Range("4:4,7:7,10:10,13:13").Select
Selection.Insert Shift:=xlDown
End Sub