help needed for sorting

[gmangad]

I have an excel file with names in Column 'A' which starts with "MR " O
"MRS " as prefix. and I have the formula in B as follows.
IF(A1="","",IF(LEFT(A1,3)="MR ",MID(A1,4,LEN(A1)-3),IF(LEFT(A1,4)="MR
",MID(A1,5,LEN(A1)-4),A1)))

It works perfect
and I am running the following macro to sort column B alphabeticaly
but after sorting, the empty rows are apprearing at beginning. Ca
anyone help me???????
Sub Macro4()
Range("A1:B" & Range("B500").End(xlUp).Row).Select
Selection.Sort Key1:=Range("b1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub

Thanks in advance
G

 

[Max]

2 possible ways to consider ?

a) If sort order *descending* for col B is acceptable,
just change "Order1:=xlAscending" to
"Order1:=xlDescending"

This will throw the empty rows to the bottom
(but the names will be in reverse alpha order)

Alternatively,

b) Change the first part of the formula in col B
to something like : "=IF(A1="","zzzzz", ..."
instead of "=IF(A1="","",..."

Then your sort ascending on col B will retain the alpha order,
with "zzzzz" rows, i.e. the previously "empty" rows
thrown to the bottom

 

[gmangad]

Hi htm
Thanks for the reply,
but this will not help me, as I need to keep the empty cell as blank
itself as it has link to some other files too.

Thanks again
God bless
GT

 

[Max]

you're welcome GT ! I've run dry, sorry.

Perhaps others would step-in with other options for you.

btw, "hth" = hope that helps !

cheers
Max

 

[Freemini]

You could use Max's response and then add the following to the end o
your Macro

columns(\"b:b\").select
selection.replace what:=\"zzz\", replacement:=\"\", lookat:=xlpart, _
searchorder:=xlbyrows, matchcase:=false

Hope this helps

Mik

 

[Dave Peterson]

Since you're already using a helper column (B?), maybe just add another formula
in one more helper column and sort by that.

=IF(B1="",REPT("z",255),B1)

Dragged down.

(With helper cells, if you're in for a penny, you're in for a pound! <bg>)