sorting data MINUS the blank lines

[the blue fox] · Jan 10th 2006

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

**iwrk4dedpr** · Jan 10th 2006

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.

[the blue fox] · Jan 10th 2006

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

**iwrk4dedpr** · Jan 10th 2006

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.

[the blue fox] · Jan 10th 2006

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

**iwrk4dedpr** · Jan 10th 2006

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.

[the blue fox] · Jan 10th 2006

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

**iwrk4dedpr** · Jan 10th 2006

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.

**iwrk4dedpr** · Jan 10th 2006

Re: sorting data MINUS the blank lines

Tayler,

This should do ya!

Code

Sub sort_descend()


 '  Local Variables
    Dim rngData             As Range


 '  Find actual range of data
    Set rngData = Range("B5", Range("B65536").End(xlUp))


'   Remove all cells in data range which trim(rngData) = ""
    For Each rngData In rngData
        If Trim(rngData) = "" Then rngData.ClearContents
    Next rngData


 '  Re-Find actual range of data and sort
    Set rngData = Range("B5", Range("B65536").End(xlUp).Offset(0, 30))


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


End Sub

Display More

[the blue fox] · Jan 10th 2006

Re: sorting data MINUS the blank lines

Thanks for your help

sorting data MINUS the blank lines

Participate now!

Share