Page 2 of 2 FirstFirst 12
Results 16 to 19 of 19
  1. #16
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,078
    Thanks
    13
    Thanked 36 Times in 35 Posts
    Here is a sample with only 1 tab and substantially reduced in row number.

    Kevin
    Attached Files Attached Files

  2. #17
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,194
    Thanks
    201
    Thanked 785 Times in 719 Posts
    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:
    ?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.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #18
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,078
    Thanks
    13
    Thanked 36 Times in 35 Posts
    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.

  4. #19
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,200
    Thanks
    46
    Thanked 228 Times in 211 Posts
    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:

    If .Cells(I, 9) = "" Or Mid(.Cells(I, 9), 1, 3) = "<space><space><space>" Then (where <space> is replaced by an actual space)
    Last edited by Maudibe; 2014-08-17 at 09:08.

Page 2 of 2 FirstFirst 12

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •