Remove spaces
 

Try this:

Sub SpaceKiller()
For Each r In ActiveSheet.UsedRange
v = r.Value
If Len(v) 0 And Len(Replace(v, " ", "")) = 0 Then
r.Clear
End If
Next
End Sub
--
Gary''s Student - gsnu201003


"QB" wrote:

I have been given an excel workbook which I need to import into Access.
After lengthy testing I found out that I have been experiencing import
problem because the excel spreadsheet contains 'blank entries' where the user
made a series of spaces but no actual text. So then Access interprets the
spaces as text, and it violate the established format.... (you get the point)

Is there a simple way for me to clean up these 'blank entries'? How can I
loop through a given spreadsheet and blank any cell with spaces (no text, no
date... just spaces)?

Thank you,

QB

Remove spaces
 

This will work
For Each r In ActiveSheet.UsedRange
If Not r.MergeCells Then
v = r.Value
If Len(v) 0 And Len(Replace(v, " ", "")) = 0 Then
r.Clear
End If
End If
Next

"QB" wrote:

Gary,

Firstly, thank you for the rapid response.

I tried your code out, and in a general sense it does work. However, it
crashes on merged cells. Is there any way to resolve this so it can either
handle the merged cells or skip over them but still process the rest?

QB




"Gary''s Student" wrote:

Try this:

Sub SpaceKiller()
For Each r In ActiveSheet.UsedRange
v = r.Value
If Len(v) 0 And Len(Replace(v, " ", "")) = 0 Then
r.Clear
End If
Next
End Sub
--
Gary''s Student - gsnu201003


"QB" wrote:

I have been given an excel workbook which I need to import into Access.
After lengthy testing I found out that I have been experiencing import
problem because the excel spreadsheet contains 'blank entries' where the user
made a series of spaces but no actual text. So then Access interprets the
spaces as text, and it violate the established format.... (you get the point)

Is there a simple way for me to clean up these 'blank entries'? How can I
loop through a given spreadsheet and blank any cell with spaces (no text, no
date... just spaces)?

Thank you,

QB