Inserting blank rows between different series of data

[mocorlando]

I would like to know how can i insert a blank row in between ACC# when ever a new series of data begins

name Acc # Balance Date
John 2222 200 june 2013
John 2222 344 june 2013
John 2222 700 Sept 2013
Silva 4444 333 June 2013
Silva 4444 333 June 2013
Silva 4444 333 June 2013
Raff 5555 897 June 2015
Raff 5555 897 June 2015
Raff 5555 897 June 2015
Raff 5555 897 June 2015

 

[jeffreybrown]

I'm guessing you have three series here...

In the next available column which I'm also guessing is E, place a 1 one the first series, two on the second, and finally a three on the third.

Now in E12 place a one, E13 a two, and E14 a three.

Place a header in E1 and sort smallest to largest by column E

 

[Trebor76]

Hi mocorlando,

Welcome to the forum!!

Try this macro:

Option Explicit
Sub InsertRowAtChangeInValue()

    'Written by Trebor76
    'Visit my website www.excelguru.net.au
    
    'Inserts a blank in-between changes in column B (the data should be sorted ny this column for best results).

    'http://www.mrexcel.com/forum/excel-questions/724371-inserting-blank-rows-between-different-series-data.html

    Dim lngMyRow As Long
    Dim lngLastRow As Long
    
    lngLastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Application.ScreenUpdating = False

    For lngMyRow = lngLastRow To 2 Step -1 'Works backwards up to row 2. Change to suit.
        If Cells(lngMyRow, "B") <> Cells(lngMyRow - 1, "B") Then 'Looks for a change in column B. Change to suit.
            Rows(lngMyRow).EntireRow.Insert
        End If
    Next lngMyRow
    
    Application.ScreenUpdating = True
    
    MsgBox "Rows have now been inserted.", vbInformation
    
End Sub

HTH

Robert

 

[mocorlando]

Thanks guys both options were usefull especially the MACO version

 

[Trebor76]

Thanks for letting us know and you're welcome

 

[mocorlando]

ok so if i would like to change the colum the macro uses as refrence can it be done? or will you have to go and write a whole new macro for me?

 

[Trebor76]

You just need to change the two references to column B from this statement...

If Cells(lngMyRow, "B") <> Cells(lngMyRow - 1, "B") Then 'Looks for a change in column B. Change to suit.

...to whatever column you want the code to run on.

Robert