Blank Rows

[BakerBaker]

Would very much appreciate assistance with the following.
I have a long list of data most of which is separated into blocks of 6 with a blank row in between. Occasionally, I encounter a block of 5 and I would like to insert a row after item 5.

1 Blue
2 Green
3 Orange
4 Red
5 Brown
6 Black
(blank row)
1 Blue
2 Green
3 Orange
4 Red
5 Brown
6 Black
(blank row)
1 Blue
2 Green
3 Orange
4 Red
5 Brown
(blank row) Want to enter an additional blank row after Item 5
1 Blue
2 Green
3 Orange
4 Red
5 Brown
6 Black
(blank row)

 

[Joe4]

Where does this data exist on your sheet?
Are they actually numbered, like you showed above?

 

[MickG]

Try this:-
Ref Column "A"

[COLOR="Navy"]Sub[/COLOR] MG20Mar58
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Range("A:A").SpecialCells(xlCellTypeConstants).Areas
    [COLOR="Navy"]If[/COLOR] Dn.Count = 5 [COLOR="Navy"]Then[/COLOR]
        Dn(Dn.Count + 1).EntireRow.Insert
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Fluff]

If the numbers are in col A starting at A1 try

Sub BakerBaker()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Replace 1, "=xxx", xlWhole, , , , False, False
      .SpecialCells(xlFormulas, xlErrors).EntireRow.Insert
      .Replace "=xxx", 1, xlWhole, , , , False, False
   End With
End Sub

Misread OP
This will not do what you are after.

 

[jim may]

A bit Crude, but the first thing that came to mind...


Excel 2010

	A	B
1 Blue
2 Green
3 Orange
4 Red
5 Brown
6 Black
1 Blue
2 Green
3 Orange
4 Red
5 Brown
6 Black
1 Blue
2 Green
3 Orange
4 Red
5 Brown
1 Blue
2 Green
3 Orange
4 Red
5 Brown
6 Black

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Test[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]17[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]TRUE[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=(AND(LEFT(A2,1)+0=5,LEFT(A3,1)+0<>6))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[BakerBaker]

Thank you very much - works exactly as I would wish.

 

[BakerBaker]

Thank you very much MickG - works exactly as I would wish.