insert a row when the data in a column changes

[eicherj]

Forgive me if I don't know what I'm talking about, I'm a newbie to Excel macros. I have a large spreadsheet with over 2,800 rows and 66 columns. The data is sorted thusly: Column A (major), column B, Column D (minor). I would like to have a macro that inserts 1 row when the data in column B changes and 2 rows when the data in column A changes.

For example:

A B C D ...
51 DOA 68 DIVISION ... 44130
52 DOA 68 DIVISION ... 46096
53 DOA 85 ASSET ... 40724
54 DOA 85 ASSET ... 47469
55 DOO 01 DISTRICT .... 30000
56 DOO 01 DISTRICT ... 30085

Should become:

A B C D ...
51 DOA 68 DIVISION ... 44130
52 DOA 68 DIVISION ... 46096
53
54 DOA 85 ASSET ... 40724
55 DOA 85 ASSET ... 47469
56
57
58 DOO 01 DISTRICT .... 30000
59 DOO 01 DISTRICT ... 30085

Thank you for any help you can give.

eicherj


EDIT - Deleted Personal Information - Moderator

I must be a trombone player. I'm always leading a parade whenever I drive on two lane roads and sometimes the drivers behind me blow their horns.

 

[kpark91]

insert the row after or before the changed row?

 

[btadams]

try this (adapted from http://www.mrexcel.com/forum/showthread.php?t=58685):

Sub InsertRows()
  Dim r As Long, mcol1 As String, mcol2 As String, i As Long

' find last used cell in Column A
  r = Cells(Rows.Count, "A").End(xlUp).Row

 ' get value of  last used cell in columna A and B
  mcol1 = Cells(r, 1).Value
  mcol2 = Cells(r, 2).Value

 ' insert rows by looping from bottom
  For i = r To 2 Step -1
     If Cells(i, 2).Value <> mcol2 Then
        If Cells(i, 1) <> mcol1 Then
            mcol1 = Cells(i, 1).Value
            mcol2 = Cells(i, 2).Value
            Rows(i + 1).Insert
            Rows(i + 1).Insert
        Else
            mcol2 = Cells(i, 2).Value
            Rows(i + 1).Insert
        End If
     End If
  Next i

End Sub

 

[eicherj]

To Kpark91:

I want to insert a row after the data changes. See the example in my original query.

 

[eicherj]

To btadams:

I'm afraid I'm at a lower level of Excel macro knowlege than you anticipated. Where do I put this? In a separate workxheet? In a VB "page"?

Thanks.

 

[VoG]

Try

Sub insrows()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    If Range("A" & i).Value <> Range("A" & i - 1).Value Then Rows(i).Insert
Next i
End Sub