Inconsistent Sorting

[kurchian]

Sometimes when I sort numbers I find that certain cells do not respond properly.

Does this have something to do with how I have formatted the numbers and if so, how can I format to ensure appropriate sorting?

[elgringo56]

In what way do they act funny? Are there any "non printible" characters in them? This has been a headache for me for a few weeks now in sorting and in filtering. click on a cell that isn't sorting right and then go up to the window that showes the contents and space thru the contents with the cursor to see if there are non printables in it.

[Asala42]

Hey,

I've seen that before - usually from data that originates from an app outside excel. You might want to try a few things:

1.Look in the cells that aren't sorting correctly- look for extra spaces/bizarre characters and so forth. If you see any and can clear them easily, try the sort again.

(or)

2. Select the column, then go to Data/Text to Columns/finish. Then try the sort again.

(or if you're dealing with number or date type of data)

3. type a 0 in a cell- hit edit/copy - highlight the problematic cells - hit edit/paste special/add.

Hopefully one of the above will nail it,
Adam

[elgringo56]

Or, you can run this routine on the area that you wish to sort and it will clean out any of the no printables. I got this from Ivan the other day and it works great. Just key it in, select the area and run it. I now use it on all my data areas that I have gotten from outside my machine before I use the data.
Dim CleanTrimRg As Range
Dim oCell As Range
Dim Func As WorksheetFunction

Set Func = Application.WorksheetFunction

On Error Resume Next
Set CleanTrimRg = Selection.SpecialCells(xlCellTypeConstants, 2)

For Each oCell In CleanTrimRg
oCell = Application.WorksheetFunction.Clean(Func.Trim(oCell))
Next