Remove spaces
in [Programming]
|
From: Gary''s Student on 27 May 2010 15:53 This version ignores merged cells: Sub SpaceKiller() 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 End Sub Have a great day! -- Gary''s Student - gsnu201003 "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
From: QB on 27 May 2010 16:27 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
From: Gary''s Student on 27 May 2010 16:55 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
From: Mike on 27 May 2010 17:12 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
|
Pages: 1 Prev: Rotate application Next: Conditional Formatting with Hyperlinks |