Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    May 2001
    Location
    Ventura, California, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Make an Empty Cell (Excel 2000 SR1)

    I have data in spreadsheets that I want to import into a database (Access), but I need to transform much of the data before it's ready. (I have to clean-up all kinds of data entered by end-users.)

    So, I use one worksheet tab to reference the raw data (in another worksheet). If the raw data cell is blank, my formula says to set the cell to "" -- =IF(ISBLANK(B2),"",N(B2)). Then, when all the data is transformed, I copy everything and Paste Special Value. The trouble is that a value of "" is not a blank cell! It contains something. And that something screws-up the import into the database (what should be number columns are interpreted as text because of the cells that are filled with "").

    QUESTION: Is there a way to actually set a cell to the equivalent of being Deleted? (If I select every 'bank' cell and hit the Delete key, it removes the trouble and enables a success import to the DB.)

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

    Re: Make an Empty Cell (Excel 2000 SR1)

    The VBA routine below should clear everything out.

    <pre>Public Sub Clean()
    Dim oCell As Range
    For Each oCell In ActiveSheet.UsedRange
    If oCell.Value = "" Then
    oCell.Clear
    End If
    Next oCell
    End Sub
    </pre>

    Legare Coleman

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

    Re: Make an Empty Cell (Excel 2000 SR1)

    It's probably putting a vbNullstring in the cell, and Legare's fix will correct it. See this <!post=post,136831>post<!/post>where I learned this lesson! Servando Villalon has a neat non-macro trick to fix this:

    1. Select the data, one column at a time
    2. select menu "Data, Text to Columns
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Star Lounger
    Join Date
    May 2001
    Location
    Ventura, California, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make an Empty Cell (Excel 2000 SR1)

    The VBA routine worked brilliantly.
    I have not tried the other suggestions (yet).

    This is fine for cleaning-up data. But I'd still prefer to avoid filling a cell with something that later needs to be cleared (after pasting special as value). So if anyone knows how to do that in a formula within a cell...

    I thank you both for your responses. You've made my life easier, today. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

Posting Permissions

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