PDA

View Full Version : Solved: sort ascending leaves blanks at the top



xltrader100
07-19-2008, 10:35 AM
I'm sorting a column of data ascending, where I would expect to see no blanks at the top, and there are hundreds of what look like blank cells at the top of the column. Anyone know what's in these cells? Sheet attached.

Simon Lloyd
07-19-2008, 11:07 AM
It looks like there were spaces, not that they were visible, or was it imported data?, anyway i cleared contents of thos "blanks" and the column was then sorted ok!

xltrader100
07-19-2008, 11:54 AM
They're not spaces because you can click in the cell and see the cursor in the formula bar is hard left.

This data was copied and pasted from the web, and I realize I can clear the blanks manually but this sorting is all done in VBA, and finding more cells like that under program control will totally mess things up. How do I keep from encountering more data like that? I don't even know what to look for.

mdmackillop
07-19-2008, 01:18 PM
If you can't avoid them, maybe you can clear them.

Sub ClearBlanks()
Dim cel as Range
Application.ScreenUpdating = False
For Each cel In Selection.SpecialCells(xlCellTypeConstants, 2)
If Len(cel) = 0 Then
cel.Value = ""
End If
Next
Application.ScreenUpdating = True
End Sub

xltrader100
07-19-2008, 02:15 PM
Thanks md, that did the trick. As luck would have it, I already had a statement -
For Each cel In Selection.SpecialCells(xlCellTypeConstants, 2)
so your little addition dropped right in there like it was tailor made.

david000
07-19-2008, 06:59 PM
They're not spaces because you can click in the cell and see the cursor in the formula bar is hard left.

This data was copied and pasted from the web, and I realize I can clear the blanks manually but this sorting is all done in VBA, and finding more cells like that under program control will totally mess things up. How do I keep from encountering more data like that? I don't even know what to look for.

It's a Microsoft 'table cell padding text code' that was uploaded to HTML and went full circle back to a Microsoft product.

You can sometimes find it by saving your worksheet as a web page then viewing the source code, or in this case you can copy and paste the data in question to Word, and you'll see the Table menu open that is the first clue then turn on "Show Paragraph Marks" the backwards P thing and that will display this type of character too.