Import leaves "dirty" cells

CORESTOCK · Mar 13th, 2002, 08:22 PM

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 · Mar 13th, 2002, 09:09 PM

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 · Mar 14th, 2002, 12:57 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 13th, 2002, 08:22 PM	#1
CORESTOCK New Member Join Date: Mar 2002 Posts: 1	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.

Mar 13th, 2002, 09:09 PM	#2
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	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

Mar 14th, 2002, 12:57 AM	#3
Ivan F Moala MrExcel MVP Join Date: Feb 2002 Location: Auckland, New Zealand Posts: 4,209	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