Sorting by name blank cell issue

[pujo]

Hello,
I am trying to sort a column after an item is removed.
Is is sorted by name. I remove the name and hit sort and it works fine but I am left with a blank cell. Is there anything I can do for this?
Here is the code.

Private Sub CommandButton1_Click()
Range("A1:A10").sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub

Thanks,
Pujo

 

[pujo]

Works great!
I do have a question if you have the time.

What is the theory of operation here, what doesn't it sort bringing the items to the top of the list.

Just trying to educate myself on this?

 

[mikerickson]

The sort alphabetizes cells with text.
Spaces come before all other letters in Excel's alphabet.

So "John Smith" comes before "JohnSmith" , " smith" comes before "smith" and " " comes before any other string.

There are basicly three ways that a cell will appear blank:
1) it is truly blank and contains nothing.
2) it holds only spaces (e.g. " ")
3) it holds a formula that evalutes to either "" or spaces
(or 4) its formatted to show nothing, e.g. font color=white or number format returns nothing)

Each of those three is treated differently by sorting, ISBLANK and other Excel features.
It took me a conscious effort over time to switch from putting spaces in a cell to deleting the cell contents. That deliberate habit has payed off with shorter formulas.

 

[pujo]

I never realized that even thou I clear the cell using "Space Bar"
that there was still data in the cell. So this makes sense. I guess I will have to be more careful when doing these things.

Thanks for the info.
Pujo