Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sorting but leaving the empty cells on top

Status
Not open for further replies.

kragster

Technical User
May 9, 2007
55
DK
Hi,
I have created a macro for sorting a table by a column:

---------------------
Sub SortByModtaget()

Range("B10:O65536").Select
Selection.Sort Key1:=Range("L10"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B10").Select

End Sub
---------------------

My problem is, that this sorts all the cells that contain data first and then places the empty cells in the bottom of the column. I need the empty cells to be in the top of the column. Any easy way to fix this?

Regards
Lars Kragh
 
Please explain further ... you said:
I need the empty cells to be in the top of the column.
... so, if there were 20 populated rows before the sort, then afterwards you'd want them in rows 65515 to 65536? That doesn't sound very useful to me.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.
 
Oh, stupid me, I didnt think it through. It wasn't what I meant, but what I meant can't be done :) I have a spreadsheet table which we use to document purchase orders. One of the columns is called "recieved". It has a conditional format on it, which says: if empty -> set background colour to red else green. Thus orders that are recieved have a date in the "recieved" cell and are therefor green. What I want is to make a sort macro that sorts the rows of the table with the orders that hasn't been recieved yet first. But it seems to be a bit more complicated than suspected.
 




Hi,

Make a helper column that has the LOW value when the column is empty, the column value otherwise. Then sort on the helper column

Skip,

[glasses] [red][/red]
[tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top