Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merging cells and eliminating spaces for empty cells | Excel Discussion (Misc queries) | |||
Removing spaces from cells | Excel Worksheet Functions | |||
how do I remove leading spaces and leave the remianing spaces w | Excel Worksheet Functions | |||
How do I count cells with text but ignore cells with spaces? | Excel Discussion (Misc queries) | |||
Spaces in cells | Excel Worksheet Functions |