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:-

    Code
    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!

Register Yourself Login