Sorting but leaving the empty cells on top

[kragster]

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

 

[GlennUK]

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.

 

[kragster]

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.

 

[SkipVought]

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,

_[red][/red]