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	1 Blue	Test
2	2 Green	FALSE
3	3 Orange	FALSE
4	4 Red	FALSE
5	5 Brown	FALSE
6	6 Black	FALSE
7	1 Blue	FALSE
8	2 Green	FALSE
9	3 Orange	FALSE
10	4 Red	FALSE
11	5 Brown	FALSE
12	6 Black	FALSE
13	1 Blue	FALSE
14	2 Green	FALSE
15	3 Orange	FALSE
16	4 Red	FALSE
17	5 Brown	TRUE
18	1 Blue	FALSE
19	2 Green	FALSE
20	3 Orange	FALSE
21	4 Red	FALSE
22	5 Brown	FALSE
23	6 Black

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B2 =(AND(LEFT(A2,1)+0=5,LEFT(A3,1)+0<>6)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[BakerBaker]

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

 

[BakerBaker]

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