Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Cheltenham, Gloucestershire, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    blank cells which aren't! (2000/2002)

    I have data which is imported into Excel from Access. There are cells which look empty and should be empty- but in fact arn't so when I try to write a function which uses ISBLANK() - it does not include them.

    Is there anyway (preferably easy!) of overcoming this please?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: blank cells which aren't! (2000/2002)

    Try the following macro:

    Sub DeleteEmptyStrings()
    Dim aCell As Range
    On Error GoTo Exit_Sub
    Application.ScreenUpdating = False
    For Each aCell In Selection
    If aCell.Value = "" Then aCell.ClearContents
    Next
    Exit_Sub:
    Application.ScreenUpdating = True
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Cheltenham, Gloucestershire, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: blank cells which aren't! (2000/2002)

    you really are a life saver today!!!!!


    Thanks

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: blank cells which aren't! (2000/2002)

    Your code will not help cells that look blank because they contain spaces. I would modify your code like this:

    <pre>Sub DeleteEmptyStrings()
    Dim aCell As Range
    On Error Resume Next
    Application.ScreenUpdating = False
    For Each aCell In Selection
    If Trim(aCell.Value) = "" Then aCell.ClearContents
    Next aCell
    On Error GoTo 0
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Legare Coleman

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: blank cells which aren't! (2000/2002)

    Yes, in general that's better. I didn't bother in this particular case, because it's unlikely that cells imported from Access contain only spaces. Access trims trailing spaces itself. Your version is preferable as a general utility.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: blank cells which aren't! (2000/2002)

    What is Access sending that Excel imports that looks blank but isn't, and that your code would fix? I am really curious as to how your code actually does anything at all. It looks like your code clears the cell contents to "" if it is already equal to "".
    Legare Coleman

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: blank cells which aren't! (2000/2002)

    Frankly, I don't really know. But I've come across several spreadsheets with imported data (from Access, DBase, ...) where cells seemed to be empty (there were definitely NO spaces), but were treated by Excel as non-empty. The macro I posted earlier takes care of this. So apparently, ClearContents does something more than setting the value to "". Again, I don't really understand what or how or why, but it works for me...

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: blank cells which aren't! (2000/2002)

    Sounds to me that what may be being imported from Access are Null values. Not that I have tested this, but there does not seem, on the face of it, anything alse in an Access field that would deliver such a performance in Excel.
    Gre

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: blank cells which aren't! (2000/2002)

    I'm encountering a similar nuisance with imports from Access, most recently with percentages imported as text, but sometimes with null fields which are seen as null text (or some <img src=/w3timages/censored.gif alt=censored border=0> thing), even though they are formatted as general. I have attached a tiny sample of one; cells which appear blank in the range A2 - P49 are not. range.ClearContents does seem to be the only way to fix the problem, and I have a macro that addresses this specific monthly import.

    Edit follows
    On going back to review, I find I don't use .ClearContents; the applicable lines in the import specific macro are:

    With Selection
    .NumberFormat = "$#,##0.00"
    End With
    For Each rngCell In Selection
    If Len(rngCell) = 0 Then rngCell.Value = rngCell.Value
    Next rngCell

    Looks meaningless but it clears up the problem
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7DS

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: blank cells which aren't! (2000/2002)

    That is very interesting:

    =IF(A2="","Y","N")

    results in Y

    =ISBLANK(A2)

    results in False

    =CODE(A2)

    results in #Value

    =LEN(A2)

    results in 0

    =ISNONTEXT(A2)

    results in False

    =ISTEXT(A2)

    results in True

    =ISNUMBER(A2)

    results in False

    There is obviously something very strange in those cells.
    Legare Coleman

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: blank cells which aren't! (2000/2002)

    Um, ah, you responded to Roberta but were you looking at my attachment when you wrote these comments? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I messed around with these "not-really-blank" cells for some time trying to figure out what they were, and gave up, choosing to write an extended version of the macro above to clean 'em up.
    -John ... I float in liquid gardens
    UTC -7DS

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: blank cells which aren't! (2000/2002)

    As far as I can see they're just nullstrings.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: blank cells which aren't! (2000/2002)

    (After messing around frantically for 30 minutes ...) You're right! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    How did you deduce that?
    -John ... I float in liquid gardens
    UTC -7DS

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: blank cells which aren't! (2000/2002)

    Hey, didn't mean to disrespect the Unkamunka! But it helps to know more specifically that they are all vbNullStrings. I proved Rory's assertion for my own satisfaction by writing a custom function:

    Function ISNULLSTRING(rngCell As Range) As String
    ISNULLSTRING = IIf(rngCell.Value = vbNullString, "NULLSTRING", "NOT NULLSTRING")
    End Function

    But I still want to know how you and Rory figured it out. (Not to mention why they get imported. Remember this?

    'Uh-huh. I know what you're thinking. Did he fire six shots or only five? Well, to tell you the truth, I forgot myself in all this excitement. But being as this is the .44 Magnum, the most powerful handgun in the world and could blow your head clean off, you have to ask yourself one question, "Do I feel lucky?" Well, do you, punk?'

    The punk surrenders, after which Dirty Harry starts to walk away, and the killer pleads "I gots to know" (how many bullets left). Harry points the 44 at him and pulls the trigger. The chamber is empty.

    Well, I just gots to know.) <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    Isn't it wonderful that the Excel lounge can bring you great moments in Cinema?
    -John ... I float in liquid gardens
    UTC -7DS

  15. #15
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: blank cells which aren't! (2000/2002)

    Try this post. Like the "back to the roots" pic.
    Gre

Page 1 of 2 12 LastLast

Posting Permissions

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