Results 1 to 8 of 8

Macro to insert new line after number increases

Threaded View

  1. #1
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Office 365
    Posts
    869

    Macro to insert new line after number increases

    Hello happy campers.
    I have a sheet with various information, in column A are the delivery reference numbers, some of which can appear any number of times according to how many lines the order will have.
    My macro currently does
    Sub sort()
    '
    ' sort Macro
    '
    
    '
        Range("A:B,D:D,F:F,H:H,I:I,L:L,M:M,N:N,P:AF,AH:AJ").Select
        Range("AH1").Activate
        Selection.Delete Shift:=xlToLeft
        ActiveWindow.ScrollColumn = 29
        ActiveWindow.ScrollColumn = 28
        ActiveWindow.ScrollColumn = 27
        ActiveWindow.ScrollColumn = 26
        ActiveWindow.ScrollColumn = 25
        ActiveWindow.ScrollColumn = 24
        ActiveWindow.ScrollColumn = 23
        ActiveWindow.ScrollColumn = 22
        ActiveWindow.ScrollColumn = 21
        ActiveWindow.ScrollColumn = 20
        ActiveWindow.ScrollColumn = 19
        ActiveWindow.ScrollColumn = 18
        ActiveWindow.ScrollColumn = 17
        ActiveWindow.ScrollColumn = 16
        ActiveWindow.ScrollColumn = 15
        ActiveWindow.ScrollColumn = 14
        ActiveWindow.ScrollColumn = 13
        ActiveWindow.ScrollColumn = 12
        ActiveWindow.ScrollColumn = 11
        ActiveWindow.ScrollColumn = 10
        ActiveWindow.ScrollColumn = 9
        ActiveWindow.ScrollColumn = 8
        ActiveWindow.ScrollColumn = 7
        ActiveWindow.ScrollColumn = 6
        ActiveWindow.ScrollColumn = 5
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 1
        Rows("1:1").Select
        Selection.Delete Shift:=xlUp
        Cells.Select
        Cells.EntireColumn.AutoFit
        Range("A1").Select
        ActiveWorkbook.Worksheets("DCS").sort.SortFields.Clear
        ActiveWorkbook.Worksheets("DCS").sort.SortFields.Add Key:=Range("A1"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("DCS").sort
            .SetRange Range("A1:G260")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End Sub
    I would now like some code that will look through Column A (now is has been sorted A-Z) and lets say the first delivery number is 1234567891, when this number then changes to the next i would like the code to insert an empty row. (see attached)

    As always, your input is much appreciated.
    galvinpaddy
    Attached Files Attached Files
    Last edited by galvinpaddy; 11-22-2011 at 11:14 AM. Reason: updated

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1