Insert a line after each group of similar cells

[Wamhoi]

Using a VBA (or any other method) how can I tell excel to insert a line after each group of similar cells for example

a
a
a
b
b
b
b
c
c

becomes:

a
a
a

b
b
b
b

c
c

 

[cstimart]

Try this:

Sub F00()
Dim blnEnd As Boolean
Dim i As Long

i = 3   'Assuming Header Row
Do While Not blnEnd
    With Cells(i, 1)
        If .Value <> .Offset(-1) Then
            .EntireRow.Insert
            i = i + 2
        ElseIf .Value = vbNullString Then
            blnEnd = True
        Else
            i = i + 1
        End If
    End With
Loop
End Sub

 

[Wamhoi]

I'm working off a 600k plus of rows worksheet. Well, lets just say your the best!!

 

[Peter_SSs]

Is your original data only in a single column? If not, can you give a few more details about what is in the sheet and where?

If you think it may be any use, you might want to test this code which doesn't actually insert a row in the sheet, but does space the data out as you described. For 600,000 rows me it takes less than 1 second, compared to about 25 seconds for the other code. It assumes the data in column A is not formulas that need to be retained.

<font face=Courier New><br><br><SPAN style="color:#00007F">Sub</SPAN> InsertSpaces()<br>    <SPAN style="color:#00007F">Dim</SPAN> a, b<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, k <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>   <br>    lr = Range("A" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">ReDim</SPAN> b(1 <SPAN style="color:#00007F">To</SPAN> lr * 2, 1 <SPAN style="color:#00007F">To</SPAN> 1)<br>    <SPAN style="color:#00007F">With</SPAN> Range("A1")<br>        a = .Resize(lr).Value<br>        b(1, 1) = a(1, 1)<br>        b(2, 1) = a(2, 1)<br>        k = 3<br>        <SPAN style="color:#00007F">For</SPAN> i = 3 <SPAN style="color:#00007F">To</SPAN> lr<br>            <SPAN style="color:#00007F">If</SPAN> a(i, 1) = a(i - 1, 1) <SPAN style="color:#00007F">Then</SPAN><br>                b(k, 1) = a(i, 1)<br>            <SPAN style="color:#00007F">Else</SPAN><br>                k = k + 1<br>                b(k, 1) = a(i, 1)<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            k = k + 1<br>        <SPAN style="color:#00007F">Next</SPAN> i<br>        .Resize(k - 1).Value = b<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br><br></FONT>