Conditional Insert Row

  • Thread starter Thread starter rlfjr75
  • Start date Start date
R

rlfjr75

Hello...I need to automatically insert my entire first row of data at
each increment where the value in Column A has changed. Is there a
function I can establish to do such a task automatically?

Example:

File format as it appears currently..

Number,Date
12345, 8/11/2004
12345, 8/11/2004
98765, 8/12/2004

File format as it needs to appear..

Number,Date
12345, 8/11/2004
12345, 8/11/2004
Number,Date
98765, 8/12/2004
 
Hi
try the following macro. It tests column B and inserts a row if
the values change
Sub insert_rows()
Dim lastrow As Long
Dim row_index As Long

lastrow = ActiveSheet.Cells(Rows.count, "B").End(xlUp).row
For row_index = lastrow - 1 To 1 Step -1
If Cells(row_index, "B").Value <> Cells(row_index + 1, "B").Value
Then
Cells(row_index + 1, "B").EntireRow.Insert (xlShiftDown)
cells(1,1).entirerow.copy destination:=Cells(row_index + 1,
"B").EntireRow
End If
Next
End Sub
 
Hi,

Try this macro(modified from Bernie Deitricks <deitbe @
consumer dot org> of earlier today)

jeff


Sub TryNow()
Dim myRow As Long
Dim myCount As Long
Dim myCol As Integer

myCol = 1
myCount = ActiveSheet.UsedRange.Rows.Count

For myRow = myCount - 1 To 1 Step -1
If Cells(myRow, myCol).Value <> Cells(myRow + 1,
myCol).Value Then
Range(Cells(myRow + 1, myCol), Cells(myRow + 1,
myCol)).EntireRow.Insert
Range(Cells(myRow + 1, myCol), Cells(myRow + 1,
myCol)).Select
ActiveCell.Value = "Number,Date"
End If
Next myRow
End Sub
 
Back
Top