Make an Empty Cell (Excel 2000 SR1)

Topic

New Reply

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.)

Reply | Quote

Replies

The VBA routine below should clear everything out.

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

Reply | Quote

It’s probably putting a vbNullstring in the cell, and Legare’s fix will correct it. See this postwhere 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

Reply | Quote

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.

Reply | Quote