MACRO help: Insert Rows after each new item in a column

[Jillbeirne]

hello!

very new to anything besides simple macros, so hopefully some kind-hearted soul will help!

i was wondering how i could create a macro to do the following:

i have a list of advertisers.. i want to insert 2 rows after each change in advertisers; so the macro should compare each row to the one before it, and insert two rows when two cells differ. is that possible? i go to page 15 in reading the other posts, but could not find anything similar!
thanks!

 

[GaryB]

This assumes that your advertisers names are in column A and there are no blank cells till the end of the list.

Sub SplitAdvertisters()
myRow = 2

Do Until Cells(myRow, 1) = ""

If Cells(myRow, 1) <> Cells(myRow - 1, 1) Then
Rows(myRow).EntireRow.Insert
Rows(myRow).EntireRow.Insert
myRow = myRow + 2
End If
myRow = myRow + 1

Loop

End Sub


HTH

GaryB

 

[Jillbeirne]

THANK YOU! this was me doing my report before your macro:

 

[George J]

My effort - late as usual but...

Sub InsertRows()
Dim r As Long
Dim adv As String
Dim i As Long

r = Cells(Rows.Count, "A").End(xlUp).Row

adv = Cells(r, 1).Value

For i = r To 2 Step -1
If Cells(i, 1).Value = "" Then
adv = Cells(i - 1, 1).Value
Else

If Cells(i, 1).Value <> adv Then
adv = Cells(i, 1).Value
Rows(i + 1).Insert
Rows(i + 1).Insert
End If
End If
Next i

End Sub

If you add extra advertisers directly under your las entry, even after sorting (not leaving a blank row) it will sort these as well.

 

[Von Pookie]

Continued at: http://www.mrexcel.com/board2/viewtopic.php?t=63743