Insert Line after specific date

[jake424]

Hello all,

In column B I i have effective dates for different items (column A). Like so:

A B
1 1/1/2011
1 1/1/2012
1 1/1/2013
2 1/1/2010
2 1/1/2011
2 1/1/2012
2 1/1/2013
3 1/1/2012
3 1/1/2013

I need to be able to find 1/1/2013 (or said specific date) in column B and insert a blank row under it (without right clicking and inserting row because there are a lot of them )

Thanks in advance for the help!

 

[Jeffrey Lopez]

If VBA is acceptable, you could use something like this:

Sub HappyNewYear()
Dim lngLstRow As Long
lngLstRow = ActiveSheet.UsedRange.Rows.Count

For i = lngLstRow To 1 Step -1
    Range("B" & i).Select
        If ActiveCell.Value = "1/1/2013" Then
            ActiveCell.EntireRow.Insert xlShiftDown
        End If
Next i

End Sub

Basically starts at the bottom of the list (Column B) and works up looking for 1/1/2013, if it finds it, it will insert an row under it...

Just looking at the data, you could do it on Column A as well, and just look for changes in numbers, 1 1 1 (New Row) 2 2 2 2 (New Row).. but since you asked for the date, I used that...

Hope that helps!

 

[jake424]

If VBA is acceptable, you could use something like this:

Sub HappyNewYear()
Dim lngLstRow As Long
lngLstRow = ActiveSheet.UsedRange.Rows.Count

For i = lngLstRow To 1 Step -1
    Range("B" & i).Select
        If ActiveCell.Value = "1/1/2013" Then
            ActiveCell.EntireRow.Insert xlShiftDown
        End If
Next i

End Sub

Basically starts at the bottom of the list (Column B) and works up looking for 1/1/2013, if it finds it, it will insert an row under it...

Just looking at the data, you could do it on Column A as well, and just look for changes in numbers, 1 1 1 (New Row) 2 2 2 2 (New Row).. but since you asked for the date, I used that...

Hope that helps!

Jeffrey, I copied the VBA and it works, except that it inserts the blank row ABOVE the date and not under it. What should be changed to make it insert a row under the date?


Thanks,
Jake

 

[Jeffrey Lopez]

Oh man sorry!! I forgot I was going from bottom to top, give this one a go... just added an offset:

Sub HappyNewYear()
Dim lngLstRow As Long
lngLstRow = ActiveSheet.UsedRange.Rows.Count


For i = lngLstRow To 1 Step -1
    Range("B" & i).Select
        If ActiveCell.Value = "1/1/2013" Then
            ActiveCell.Offset(1, 0).EntireRow.Insert xlShiftDown
        End If
Next i
End Sub

 

[Rick Rothstein]

Here is another way to do it (without using a loop)...

Sub InsertRowsAfterJan1st2013()
  Dim InsertAtDate As String
  With Columns("B")
    InsertAtDate = "1/1/2013"
    .Cells.Replace InsertAtDate, "#N/A", xlWhole
    .SpecialCells(xlConstants, xlErrors).Offset(1).EntireRow.Insert
    .Cells.Replace "#N/A", InsertAtDate, xlWhole
  End With
End Sub

 

[jake424]

Thank you both so much! One more (and hopefully quick) question.

How can I copy the text from the above row/cells to the newly inserted row? For instance, if I have data in rows A, C, D, and E that I want copied down (all but the date in row B), how would I do that?


Thanks again for your help!