Finding spaces cells
 

Excel 2002, Win XP

I'm helping an OP who is transferring data to Excel from somewhere and from
Excel to a database program, usually Access. The data consists of some
cells that have:

Data and spaces -that's OK

Blank cells - that's OK

Cells with one or more spaces and nothing else - That's not OK. I'll call
them "spaces cells" for this post. My problem is that the number of spaces
can vary.

He needs to blank (clear) all the "spaces cells".

All I can think of is to:

Loop through all the cells in the used range

Check for Len<0

Then loop through all the characters of that cell and if I find any
character other than the space character, go to the next cell, otherwise
clear that cell.

Is there an easier (faster) way to do this? Thanks for your help. Otto

Finding spaces cells
 

Still runs through every cell, but might be a bit quicker:-

Sub ClearSpaces()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each cell In ActiveSheet.UsedRange
If Len(cell) 0 And _
Len(Application.WorksheetFunction.Substitute(cell. Value, " ", "")) = 0 Then
cell.ClearContents
End If
Next cell

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Otto Moehrbach" wrote in message
...
Excel 2002, Win XP

I'm helping an OP who is transferring data to Excel from somewhere and from
Excel to a database program, usually Access. The data consists of some
cells that have:

Data and spaces -that's OK

Blank cells - that's OK

Cells with one or more spaces and nothing else - That's not OK. I'll call
them "spaces cells" for this post. My problem is that the number of spaces
can vary.

He needs to blank (clear) all the "spaces cells".

All I can think of is to:

Loop through all the cells in the used range

Check for Len<0

Then loop through all the characters of that cell and if I find any
character other than the space character, go to the next cell, otherwise
clear that cell.

Is there an easier (faster) way to do this? Thanks for your help. Otto

Finding spaces cells
 

I suggest you Loop through all the cells in the used range
and use the following:

activecell.value = Trim(activecell.value)

this will get rid of all leading and trailing spaces
(which may not be desireable). but if leading and trailing
spaces are not a concern this will work.






-----Original Message-----
Excel 2002, Win XP

I'm helping an OP who is transferring data to Excel from
somewhere and from
Excel to a database program, usually Access. The data
consists of some
cells that have:

Data and spaces -that's OK

Blank cells - that's OK

Cells with one or more spaces and nothing else - That's
not OK. I'll call
them "spaces cells" for this post. My problem is that
the number of spaces
can vary.

He needs to blank (clear) all the "spaces cells".

All I can think of is to:

Loop through all the cells in the used range

Check for Len<0

Then loop through all the characters of that cell and if
I find any
character other than the space character, go to the next
cell, otherwise
clear that cell.

Is there an easier (faster) way to do this? Thanks for
your help. Otto


.

Finding spaces cells
 

Make sure you trap for any formulas in the cells though (assuming there can be any), else they
will be converted to values, eg:-

Sub ClearSpaces()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each cell In ActiveSheet.UsedRange
If cell.HasFormula = False Then
cell.Value = Trim(cell.Value)
End If
Next cell

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"John Flynn" wrote in message ...
I suggest you Loop through all the cells in the used range
and use the following:

activecell.value = Trim(activecell.value)

this will get rid of all leading and trailing spaces
(which may not be desireable). but if leading and trailing
spaces are not a concern this will work.






-----Original Message-----
Excel 2002, Win XP

I'm helping an OP who is transferring data to Excel from
somewhere and from
Excel to a database program, usually Access. The data
consists of some
cells that have:

Data and spaces -that's OK

Blank cells - that's OK

Cells with one or more spaces and nothing else - That's
not OK. I'll call
them "spaces cells" for this post. My problem is that
the number of spaces
can vary.

He needs to blank (clear) all the "spaces cells".

All I can think of is to:

Loop through all the cells in the used range

Check for Len<0

Then loop through all the characters of that cell and if
I find any
character other than the space character, go to the next
cell, otherwise
clear that cell.

Is there an easier (faster) way to do this? Thanks for
your help. Otto


.

Finding spaces cells
 

Good point Ken. I don't have any formulas in this data but that's a good
point to be aware of for the future. Otto
"Ken Wright" wrote in message
...
Make sure you trap for any formulas in the cells though (assuming there
can be any), else they
will be converted to values, eg:-

Sub ClearSpaces()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For Each cell In ActiveSheet.UsedRange
If cell.HasFormula = False Then
cell.Value = Trim(cell.Value)
End If
Next cell

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

--------------------------------------------------------------------------
--
Attitude - A little thing that makes a BIG difference
--------------------------------------------------------------------------
--



"John Flynn" wrote in message
...
I suggest you Loop through all the cells in the used range
and use the following:

activecell.value = Trim(activecell.value)

this will get rid of all leading and trailing spaces
(which may not be desireable). but if leading and trailing
spaces are not a concern this will work.






-----Original Message-----
Excel 2002, Win XP

I'm helping an OP who is transferring data to Excel from
somewhere and from
Excel to a database program, usually Access. The data
consists of some
cells that have:

Data and spaces -that's OK

Blank cells - that's OK

Cells with one or more spaces and nothing else - That's
not OK. I'll call
them "spaces cells" for this post. My problem is that
the number of spaces
can vary.

He needs to blank (clear) all the "spaces cells".

All I can think of is to:

Loop through all the cells in the used range

Check for Len<0

Then loop through all the characters of that cell and if
I find any
character other than the space character, go to the next
cell, otherwise
clear that cell.

Is there an easier (faster) way to do this? Thanks for
your help. Otto


.