Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Cells look empty but are not (XP)

    I frequently receive spreadsheet files imported from other programs. It is not uncommon to have cells that look empty but are not. Whatever is in those cells plays havoc with formulas. I have attached a small sample. I would like to know what is really there and a simple method of cleaning the worksheet up or writing formulas that can accommodate the situation.

    Thank you.

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

    Re: Cells look empty but are not (XP)

    This is dangerous if the seemingly blank cells are interspersed with cells containing formulas. Perhaps this alternative?

    Sub FixBlanks()
    Dim oCell As Range
    For Each oCell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConst ants).Cells
    If Trim(oCell.Value) = "" Then
    oCell.Value = ""
    End If
    Next oCell
    End Sub

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

    Re: Cells look empty but are not (XP)

    You are absolutely correct, but your fix also has a problem. If you have a formula like this:

    =IF(A2="","","xx")

    that returns a null string, then your code will also delete the formula. I have edited my original post with a change that should fix that also.
    Legare Coleman

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

    Re: Cells look empty but are not (XP)

    The cells contain a null string. Select the cells and run the macro below:

    <code>
    Public Sub FixNullStrings()
    Dim oCell As Range
    For Each oCell In Selection
    If Not (oCell.HasFormula) And Trim(oCell.Value) = "" Then
    oCell.Value = Trim(oCell.Value)
    End If
    Next oCell
    End Sub
    </code>


    The code above will not fix cells that contain one or more blank characters. If you also want to remove those, then use this:

    <code>
    Public Sub FixNullStrings()
    Dim oCell As Range
    For Each oCell In Selection
    If Not (oCell.HasFormula) Then
    oCell.Value = Trim(oCell.Value)
    End If
    Next oCell
    End Sub
    </code>
    Legare Coleman

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

    Re: Cells look empty but are not (XP)

    Your code is fine, but mine too, I think. SpecialCells(xlCellTypeConstants) excludes cells with formulas, even if they result in a blank.

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

    Re: Cells look empty but are not (XP)

    You are correct, sorry about that. I didn't see the For statement, and I just copied your If statement and pasted it into my already existing routine to test.
    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Kendrick, Idaho, USA
    Posts
    277
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Cells look empty but are not (XP)

    My thanks to everyone! It's gratifying to ask a question that results in this kind of response. It proves there is no neat, easy search & replace type response and I was right to ask for help! Thanks again.

Posting Permissions

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