Data sort is placing blanks at top

[jday]

I have a list of formulas in cells A4:A53. The result of each formula will
be either a text description, or "blank" depending on the result of the IF
statement contained within. The problem is, I have a macro that copies the
'values' of range A2:A50 into B4:B53, then tries to sort ascendingly. When I
do this, all of the "blanks" appear at the top while the remaining text
descriptions fall ascendingly below. I need the "blanks" to be at the
bottom, but cannot figure out how to do this. FYI, when you look in the
cells of the "blanks", there is an apostrophe to indicate left-alignment. I
think this is causing the issue, but can't figure out what to do---I have
tried formatting B2:B50 both as "general" and "text"---both end up with the
same result. Here is the code I am using:

Range("A4:A53").Select
Selection.Copy
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
With ActiveWorkbook.Worksheets("Custom").Sort
.SetRange Range("B4:B53")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("C1").Select
Application.CutCopyMode = False

 

[Gary''s Student]

Have the macro do exactly what you would do manually. In B1 enter:

=IF(A1="",2,1) and copy down.

The sort both columns first by column B and then by column A. If we start
with:

joe 1
2
max 1
2
fred 1
2
claence 1
2
albert 1
2
zoe 1


we will end up with:

albert 1
claence 1
fred 1
joe 1
max 1
zoe 1
2
2
2
2
2

 

[Joel]

Sub test()
With Range("A4:A53")
.Copy
.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
.Sort _
Order1:=Range("B4"), _
Header:=xlNo, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
SortMethod:=xlPinYin
End With

FirstRow = Range("B4").End(xlDown).Row
If FirstRow <> 4 Then
Rows("4:" & (FirstRow - 1)).Delete
End If
End Sub