sorting data MINUS the blank lines

  • Hi all ..


    Have a macro that sorts Column B ascending from B5:B3005.
    Data can be on any line within that 3000 line range as multiple users input in certain line ranges, so there are blank lines in between all data.


    But when I sort ascending, ALL blank lines in between are pushed to the bottom .. all the data is listed in one long list.


    When I go to sort it descending .. it puts all those blank lines at the top.


    Any way I can remove the blank lines and still sort descending?
    This is the code I use so far for the sort_descend


    [vba]
    Sub sort_descend()
    '
    ' sort_descend Macro
    ' Macro recorded 04/01/2006
    '


    '
    Range("B5:AF3005").Select
    Selection.Sort Key1:=Range("B5"), Order1:=xlDescending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    Range("B5").Select

    End Sub
    [/vba]


    Hope someone can help

    Tayler [the blue fox] :thanx:

  • Re: sorting data MINUS the blank lines


    the blue fox,


    After testing the following is what I noticed. If the cells in B5 are really "" then the sort order does not matter. Meaning that the data is sorted the way that you want.


    If the cells in B5 have been blanked out with a "space" " " character then when you sort the blanks can be at the top.


    So you have the following choice ( at least it's all I can think of right now )


    1. Before sorting loop thorough all the cells in the B column and clear the cells with a " " value or if the trim(B#) value = "" then B#.clearcontents.

  • Re: sorting data MINUS the blank lines


    Sorry .. I should have said that the empty line DO have formulas in them.
    The sheet that I am sorting is dragging across the information from the 3 users who have seperate sheets they enter data on.


    Is there a way to sort only cells that have data entered?
    Or to delete cells that have no data (but they would still have their formula) then sort the sheet range?


    Hope this helps to help me :?

    Tayler [the blue fox] :thanx:

  • Re: sorting data MINUS the blank lines


    Tayler,


    So, you're saying that the "ALL" cells in column B all contain a formula, correct?


    If so then what you'll need to do is something like the following.


    1. Sort the list ascending.
    2. Redefine the range to sort so that you don't include the cells which have and APPEARENT "" value and then sort descending.



    Let me know if you need help redefining your range. Or anything else.

  • Re: sorting data MINUS the blank lines


    That's exactly what I need help with.


    I am unsure of the coding behind sorting and removing the lines that have no data.


    I can sort ascend and descend, but to remove the lines without data is stumping me.


    Tayler

    Tayler [the blue fox] :thanx:

  • Re: sorting data MINUS the blank lines


    Tayler,


    Ok give me a minute. My computer is running a database update right now and I can't get into XL for a couple of minutes.

  • Re: sorting data MINUS the blank lines


    Hi again ..


    I have been able to remove the formulas from the cells.


    So now .. all that is in this sheet is the information entered by the users.


    Again .. looking for code to .. I guess first remove the blank cells, then sort descending



    thanks

    Tayler [the blue fox] :thanx:

  • Re: sorting data MINUS the blank lines


    Well this is a bummer.


    Something must have hiccup'ed. I had posted an explanation I guess either it didn't really submit but I'll give you something based on this last post.

  • Re: sorting data MINUS the blank lines


    Tayler,


    This should do ya!