Here is a sample with only 1 tab and substantially reduced in row number.

Kevin

2014-08-16, 18:40

RetiredGeek

Kevin,

I've isolated the problem which lies in the sorting of Column I.
A decending Alpha sort sorts Z-A then numbers then blanks.
However, it appears that some of the items in Column I are NOT blank!
They appear to be blank, however, they show a length of 67!!!!!
If you click you cursor into the formula box near the right side then look where the cursor starts blinking you'll see there is something there, what I don't know.

On further investigation it seems that almost all values in Col I are 67 characters long.
Ex:

Quote:

?Len([i9])
67
?[i9].value
2081 HUNTERS RUN

I can only assume that you are importing this data from an external system.

If I were you I'd work with the exporter to get a more normal data file if possible.

Sorry, but I'm as whits end as how to code for this data set to get rid of those blanks w/o killing real data. :cheers:

2014-08-16, 20:00

kweaver

Yes, RG, the data came from an internet source. I realized when I was doing this manually and then doing some look-ups on the data that there were unusual lengths, so in my master file I created a few add'l columns and did a trim and then replaced the originals for my look-ups to work. I didn't give that a thought re the macro you did in the test file I created. Sorry for that oversight.

2014-08-17, 08:36

Maudibe

RG/KW,

I took one of the cells in column 9 that have invisible characters and ran this code:

Code:

Public Sub TestChar()
For I = 1 To Len(Range("i5"))
Debug.Print Asc(Mid([i5], I, 1))
Next I
End Sub

It returned the ASCII values of 32 so they are all a concatenation of spaces. Hopefully. you can continue to filter them out. Could you employ something like: