Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove spaces | Excel Programming | |||
Remove spaces | Excel Programming | |||
How can i remove spaces? | Excel Discussion (Misc queries) | |||
how do I remove leading spaces and leave the remianing spaces w | Excel Worksheet Functions | |||
How do I remove spaces | Excel Discussion (Misc queries) |