Results 1 to 9 of 9

macro to insert rows after each change in a sorted list

This is a discussion on macro to insert rows after each change in a sorted list within the Excel Questions forums, part of the Question Forums category; i need to extend a simple macro which sorts a list and get it to also insert a blank row ...

  1. #1
    New Member
    Join Date
    Mar 2004
    Location
    Birmingham
    Posts
    2

    Default macro to insert rows after each change in a sorted list

    i need to extend a simple macro which sorts a list and get it to also insert a blank row after each change in the sorted column. a bit like subtotals, but with blanks instead of subtotals subtotals.

    can it be done just in excel, or will it have to be done in vba?

    any suggestions?

    thanks

  2. #2
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    Hi - Welcome to the board

    This would need to be done with vba. Where do you want the rows to be inserted?

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Essex, England
    Posts
    458

    Default

    I think you'll need to do this in VBA

    Here's a simple one that should work for you. This assumes your list is in Column A, you would need to amend the Cells(myRow,1) to suit if its in a different column.


    Code:
    Sub SplitList()
    '
    ' SplitList Macro
    ' Macro recorded 05/03/2004 by GaryB
    '
    Dim myRow As Long
    
    myRow = 3   'or use 2 if you haven't got a header
    Do Until Cells(myRow, 1) = ""
    If Cells(myRow, 1) = Cells(myRow - 1, 1) Then
    myRow = myRow + 1
    Else
    Cells(myRow, 1).EntireRow.Insert
    myRow = myRow + 2
    End If
    Loop
    '
    End Sub
    Hope This Helps

    GaryB

  4. #4
    Board Regular
    Join Date
    Feb 2003
    Location
    Luton, England.
    Posts
    8,115

    Default Re: macro to insert rows after each change in a sorted list

    Seems sensible to add to the code :-

    Code:
    Sub test()
        rw = 1
        myval = ActiveSheet.Cells(rw, 1).Value
        While myval <> ""
            While myval = ActiveSheet.Cells(rw, 1).Value
                rw = rw + 1
            Wend
            ActiveSheet.Rows(rw).EntireRow.Insert
            rw = rw + 1
            myval = ActiveSheet.Cells(rw, 1).Value
        Wend
    End Sub
    Regards
    BrianB (using XL2003 & 2010)
    Most problems occur from starting at the wrong place.
    Use a cup of coffee to speed up all Windows processes.
    It is easy until you know how.
    **FORMATTED/COMMENTED CODE IS MORE LIKELY TO GET A REPLY

  5. #5
    New Member
    Join Date
    Mar 2004
    Location
    Birmingham
    Posts
    2

    Default Re: macro to insert rows after each change in a sorted list

    thanks garyb and brianb.

    both your codes worked great and i managed to adjust them to work for different columns and insert 2 rows each time.

    now i'm spoilt for choice as to which one i should use!!!

    cheers

  6. #6

    Join Date
    Sep 2003
    Posts
    994

    Default Re: macro to insert rows after each change in a sorted list

    This can be done manually, without a macro - here's a macro based on a manual method that uses worksheet formulas, sequential numbering, and sorting.

    If there are a lot of data rows to be processed, the following should be materially quicker than code that uses a loop.

    Assumed that row 1 is a header row :-

    Code:
    Sub Insert_Blank_Rows()
    Dim rng As Range, cell As Range
    Application.ScreenUpdating = False
    Columns(1).Insert
    [A2] = 1
    Set rng = Range([A3], [B65536].End(xlUp)(1, 0))
    With rng
        .FormulaR1C1 = "=IF(RC[1]=R[-1]C[1],R[-1]C,R[-1]C+1)"
        .Value = .Value
    End With
    Set cell = [A65536].End(xlUp)
    cell(2) = 1
    cell(2).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=cell - 1
    Range([A2], [A65536].End(xlUp)).EntireRow.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo
    Columns(1).Delete
    Application.ScreenUpdating = True
    End Sub

  7. #7
    New Member
    Join Date
    Mar 2004
    Location
    London
    Posts
    47

    Default Re: macro to insert rows after each change in a sorted list

    Hi there,

    Like the macro but is it possible to sum an amount after each new insert.

    I have a list of data and need to insert a new line for each new number (as in the current macro) and also sum a different column.

    account num bill value
    620013199232 30 30.40
    620014507953 32 4.63
    620015266401 1 4.57
    620030120195 1 5.01

    New line between each bill and sum value.

    Hope this makes sense.

    Gwyn

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Essex, England
    Posts
    458

    Default Re: macro to insert rows after each change in a sorted list

    I hope I'm reading the layout of your data correctly - this will subtotal Column C and insert an extra row after each SubTotal.

    Code:
    Sub SplitListAndSubTotal()
    '
    ' SplitListAndSubTotal Macro
    ' Macro recorded 19/03/2004 by GaryB
    '
    Dim myRow As Long
    Dim MyStart As Long
    
    MyStart = 2
    myRow = 3   'or use 2 if you haven't got a header
    
    Do Until Cells(myRow, 1) = ""
    If Cells(myRow, 1) = Cells(myRow - 1, 1) Then
    myRow = myRow + 1
    Else
    Cells(myRow, 1).EntireRow.Insert
    Cells(myRow, 3) = Application.WorksheetFunction.Sum(Range(Cells(MyStart, 3), Cells(myRow - 1, 3)))
    Cells(myRow + 1, 1).EntireRow.Insert
    myRow = myRow + 3
    MyStart = myRow - 1
    
    End If
    Loop
    
    Cells(myRow, 3) = Application.WorksheetFunction.Sum(Range(Cells(MyStart, 3), Cells(myRow - 1, 3)))
    
    
    '
    End Sub

    Hope This Helps

    GaryB

  9. #9

    Join Date
    Sep 2003
    Posts
    994

    Default Re: macro to insert rows after each change in a sorted list

    Quote Originally Posted by gwynwjones
    Hi there,

    Like the macro but is it possible to sum an amount after each new insert.

    I have a list of data and need to insert a new line for each new number (as in the current macro) and also sum a different column.

    account num bill value
    620013199232 30 30.40
    620014507953 32 4.63
    620015266401 1 4.57
    620030120195 1 5.01

    New line between each bill and sum value.

    Hope this makes sense.

    Gwyn
    You don't need a macro to do this - just use Xl's SubTotal tool

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com