sorting continuos blocks of data in column A

  • hi

    i have several 1000 lines of blocks of data containg numerical data separated by a space but each block is headed by the same title for example

    Cell Content
    A1 salesNumbers
    A2 5
    A3 8.5
    A4 2
    A5 9
    A6 Empty Row
    A7 SalesNumbers
    A8 55
    A9 45
    A10 66
    A11 Empty Row
    A12 Salesnumbers

    etc etc so when sorted A2=2 A3=5 A4=8.5 A5=9
    A8 = 45 A9 = 55 A10 = 66

    The title is always the same and not important to keep

    there is data to the sides of column A (in B to G) that i want sorting along with column A

    I want to sort each block of numbers and then move onto the next block (as shown above)

    keeping the empty row is not important.

    pseudo code
    a1 select
    select range to empty row
    sort data
    move down
    select next range to next empty row
    sort data

    stop when more than 3 empty rows detected (2 would be ok but 3 for safety incase extra row sneaked in)

    my pseudo code is a guess on what the coding might be , i've combed the sites and cannot find a solution anywhere and this is doing me in , hopefully simple to a guru

    many thanks in advance

  • Re: sorting continuos blocks of data in column A

    Try this:-

    Sub aSort
    Dim rng As Range, Dn As Range
    Set rng = Range("A:A").SpecialCells(xlCellTypeConstants, 1)
    For Each Dn In rng.Areas
        Dn.Sort Dn(1), xlAscending
    Next Dn
    End Sub
  • Re: sorting continuos blocks of data in column A

    hi, sadly error saying cells not found on line Set rng = Range("A:A").SpecialCells(xlCellTypeConstants, 1)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!