Import leaves "dirty" cells

[CORESTOCK]

Importing from Access to Excel leaves blank cells. Only after clearing contents of the individual blank cells will the formulas work...some Excel KB articles refer to dirty cells...what can I do to globally clean the dirty cells.

 

[Derek]

Hi
If the cells are "dirty" because they contain spaces, you can remove them by going to
Edit, Replace, in the find-what box enter * then press your spacebar once. The replace-with-what box should be completely blank. You must ensure the box "find entire cells only" is selected. Click Replace All
Hope this helps
regards
Derek

 

[Ivan F Moala]

Try running this macro to clean & trim the
data cells

Sub Clean_Trim()
Dim CleanTrimRg As Range
Dim oCell As Range
Dim Func As WorksheetFunction

Set Func = Application.WorksheetFunction

On Error Resume Next
Set CleanTrimRg = Selection.SpecialCells(xlCellTypeConstants, 2)
If Err Then MsgBox "No data to clean and Trim!": Exit Sub

For Each oCell In CleanTrimRg
oCell = Application.WorksheetFunction.Clean(Func.Trim(oCell))
Next


End Sub


Ivan