macro to insert rows between sets of records

[felder]

I have data that looks like this:

name class title
joe class1 assistant
mary class1 assistant
zelda class1 assistant
al class1 professor
ben class1 professor
cleo class1 professor
dan class1 professor
alice class2 student
barb class2 student
claire class2 student

...and so on the spreadsheet goes. What I want to do is create a macr
that will insert 2 blank lines between each 'title' group and insert
line above the groupings with the title on it.. so that the resultin
spread sheet looks like:

name class title

assistant
joe class1 assistant
mary class1 assistant
zelda class1 assistant


professor
al class1 professor
ben class1 professor
cleo class1 professor
dan class1 professor


student
alice class2 student
barb class2 student
claire class2 student

Can someone help me with such a macro for Excel?
Thanks
A

 

[Bernie Deitrick]

AA,

Try the sub below. Change the

myCol = 3

to correspond to your column of titles (column C = 3)

HTH,
Bernie
MS Excel MVP

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

myCol = 3
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 + 2, myCol)).EntireRow.Insert
Cells(myRow + 2, 1).Value = Cells(myRow + 3, myCol).Value
End If
Next myRow
End Sub