Finding spaces cells

[Otto Moehrbach]

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

 

[Ken Wright]

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

 

[John Flynn]

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.

 

[Ken Wright]

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

 

[Otto Moehrbach]

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