add rows

[REGENT]

What function can I use to add a row to a large spreadsheet at each change in
a value of a specified column

Desperate - the manual process is overwhelming.

 

[Mike H]

Hi,

Right click the sheet tab, view cod and paste this in. Changer the column as
required

Sub sonic()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For x = lastrow To 2 Step -1
If Cells(x, 1).Value <> Cells(x - 1, 1).Value Then
Cells(x, 1).EntireRow.Insert
End If
Next
End Sub

Mike

 

[JMay]

Can't be done with a function -- must be done with code. Paste the below
into a standard module of your workbook. Below assumes that Column A is the
column that will insert a new row between change.

Be sure that you first have sorted your data on Column A is this example
first.


Sub InsertRow_A_Chg()
Dim Lrow As Long, vcurrent As String, i As Long
'// find last used cell in Column A
Lrow = Cells(Rows.Count, "A").End(xlUp).Row
'// get the value of that cell in Column A (column 1)
vcurrent = Cells(Lrow, 1).Value
'// rows are inserted by looping from bottom up
For i = Lrow To 2 Step -1
If Cells(i, 1).Value <> vcurrent Then
vcurrent = Cells(i, 1).Value
Rows(i + 1).Resize(2).Insert 'Rows(i + 1).Insert to only Insert One
Blank Row
End If
Next i
End Sub

 

[Don Guillett]

This should do it. Please do not indicate desperate in your post. All get
the same priority.

Sub addrowifchange()
mc = "a"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If Cells(i - 1, mc) <> Cells(i, mc) Then Rows(i).Insert
Next i
End Sub

 

[Roger Govier]

Hi

Try the following macro

Sub insertblankrows()
Dim i As Long, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = lr To 2 Step -1
If Cells(i, 1) <> Cells(i - 1, 1) Then
Rows(i).Insert
i = i - 1
End If
Next i
End Sub


Copy the Code above
Alt+F11 to invoke the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Select sheet containing the PT's
Highlight the macro name
Run