Insert a blank row below a sequencial group

  • I need to insert a blank line at the end of a series of quantity ‘1’ in column D as I need to create two sub totals per person in column D – one for where the units are 1 and one for where the units are anything bar 1 – could anyone help me with a script for this?


    I am using the below script to insert a blank row where the name changes in Column A and then I need a row added for when the series of ‘1’ finishes in column D


    So in the example below I need a blank in Dalton below the 4th row – can anyone help


    [TABLE="width: 546"]

    [tr]


    [td]

    Claire Easter

    [/td]


    [td]

    Support Worker

    [/td]


    [td]

    Hawthorn Way

    [/td]


    [td]

    12.50

    [/td]


    [td]

    11.99

    [/td]


    [/tr]


    [tr]


    [td]

    Claire Easter

    [/td]


    [td]

    Support Worker

    [/td]


    [td]

    Hawthorn Way

    [/td]


    [td]

    14.50

    [/td]


    [td]

    11.99

    [/td]


    [/tr]


    [tr]


    [td]

    Claire Easter

    [/td]


    [td]

    Support Worker

    [/td]


    [td]

    Hawthorn Way

    [/td]


    [td]

    14.50

    [/td]


    [td]

    11.99

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Dalton Mudede

    [/td]


    [td]

    Support Worker

    [/td]


    [td]

    Hawthorn Way

    [/td]


    [td]

    1.00

    [/td]


    [td]

    78.56

    [/td]


    [/tr]


    [tr]


    [td]

    Dalton Mudede

    [/td]


    [td]

    Support Worker

    [/td]


    [td]

    Hawthorn Way

    [/td]


    [td]

    1.00

    [/td]


    [td]

    78.56

    [/td]


    [/tr]


    [tr]


    [td]

    Dalton Mudede

    [/td]


    [td]

    Support Worker

    [/td]


    [td]

    Hawthorn Way

    [/td]


    [td]

    1.00

    [/td]


    [td]

    78.56

    [/td]


    [/tr]


    [tr]


    [td]

    Dalton Mudede

    [/td]


    [td]

    Support Worker

    [/td]


    [td]

    Hawthorn Way

    [/td]


    [td]

    1.00

    [/td]


    [td]

    78.56

    [/td]


    [/tr]


    [tr]


    [td]

    Dalton Mudede

    [/td]


    [td]

    Support Worker

    [/td]


    [td]

    Hawthorn Way

    [/td]


    [td]

    7.50

    [/td]


    [td]

    11.99

    [/td]


    [/tr]


    [tr]


    [td]

    Dalton Mudede

    [/td]


    [td]

    Support Worker

    [/td]


    [td]

    Hawthorn Way

    [/td]


    [td]

    7.50

    [/td]


    [td]

    11.99

    [/td]


    [/tr]


    [/TABLE]



    Sub AddBlankRows()
    '
    Dim iRow As Integer, iCol As Integer
    Dim oRng As Range


    Set oRng = Range("a1")


    iRow = oRng.Row
    iCol = oRng.Column


    Do
    '
    If Cells(iRow + 1, iCol) <> Cells(iRow, iCol) Then
    Cells(iRow + 1, iCol).EntireRow.Insert Shift:=xlDown
    iRow = iRow + 2
    Else
    iRow = iRow + 1
    End If
    '
    Loop While Not Cells(iRow, iCol).Text = ""
    '
    End Sub

  • Re: Insert a blank row below a sequencial group


    This works with the attached file, it may have to be adapted for your workbook.


    The format of the numbers in column D are number with 1 decimal place.


    Files

    • marcidee.xlsm

      (16.16 kB, downloaded 49 times, last: )

    Bruce :cool:

  • Re: Insert a blank row below a sequencial group


    I think you need to add more than 1 blank row. Code needs to check if the sequence of 1s ends and if there is a matching sequence that ends. As much as I despise using Select this is one instance where it rocks.


    Code
    1. Sub AddBlankRows()
    2. Dim rng As Range
    3. Set rng = Range("E1", Range("E65536").End(xlUp)).Offset(, 1)
    4. rng.Offset(1) = "=IF(AND(ROW()<>2,D1<>D2),1,0)"
    5. rng.AutoFilter 1, 1
    6. rng.Offset(1).SpecialCells(xlCellTypeVisible).Select
    7. rng.AutoFilter
    8. Selection.EntireRow.Insert
    9. End Sub


    File attached to prove workings.


    Take it easy


    Smallman

  • Re: Insert a blank row below a sequencial group


    Thank you so much for replying - unfortunately this doesn't work my file - I need a line (to insert a total) under either a 1 or group of ones and then under the remainder - I will attach my file where I have put comments in column G to help you understand what I need to achieve

  • Re: Insert a blank row below a sequencial group


    I have uploaded a second spreadsheet as I have just been informed the result are not just per person but also by location - again I have put in column G what it is I need to achieve - I hope you can help me.


    Thanks