VB code to remove spaces in a string

How do I tell the function to look at all the cells in column A?

Also, How would I make it so it does not delete dublicate characters?

I tried find and replace early on and I was unsuccesful in getting it to work.

Thanks for the suggetion
Montrof

montrof,
Are the spaces really there in your strings? If you format the cell with a custom number format like ### ## #### (like a social security number) there will be spaces in the printout and screen display, but not in the value stored in the cell. Find and Replace won't get rid of them--you'll have to change the cell format instead.
Brad

Try the substitute function:

=SUBSTITUTE(A1," ","")

Place it in column B for example, it will remove all spaces. You can then copy this column to column A by only pasting the values: Edit -> Paste Special -> Values

Posted too soon.

I do agree with Brad that if find and replace does not work it might be the formatting. Although you state that the spaces are random, so that would rule out formatting.

There is no formating set to the column.  It is set to General.

I have no formatting on any of the cells.

The reason I would like to use a macro is that each month people will be dumping 1000s of account numbers into column a and then I need to make sure that there are no spaces in the account strings, so that i can upload them.

I assume that your account numbers in Column A are values not formulas?

I tried Brad's code and it worked fine in Excel 2003 but it threw up an error in Excel 2000, but when I cut the Replace method down to three arguments as below it worked ok

Sub Rep()
    Dim MyRange As Range
    Set MyRange = Intersect(Columns("a:a"), ActiveSheet.UsedRange)
    MyRange.Replace What:=" ", Replacement:="", LookAt:=xlPart
End Sub

If this still doesn't work then this more complex RegExp sub may be worth a shot

Sub BlankDel()
    Dim Reg
    Dim MyRange As Range
    Dim Cel As Range
    Set MyRange = Intersect(Columns("a:a"), ActiveSheet.UsedRange)
    Set Reg = CreateObject("vbscript.regexp")
    Reg.Global = True
    For Each Cel In MyRange
        Reg.Pattern = "\s+"
        Cel.Value = Reg.Replace(Cel.Value, "")
    Next
End Sub

Cheers

Dave

I am getting an error when I try to run the macro that stops at the following line

Set Reg = CreateObject("vbscript.regexp")

The error is as follows;
Run time error '432'
File name or class name not found during Automation Operation

Thanks
Montrof

I found this macro posted and it seems to work
Sub RemoveSpaces()
 
  'Declare variables
  Dim ws As Worksheet
  Dim rng As Range
  Dim arr As Variant
  Dim r As Long
 
  'Rng refers to the used range in the first column
  'of the active sheet
  Set ws = ActiveSheet
  Set rng = ws.UsedRange.Columns(1)
 
  'Read the values of rng into array
  arr = rng.Value
 
  'Loop through all rows in the array
  For r = 1 To rng.Rows.Count
    'Remove spaces
    arr(r, 1) = WorksheetFunction.Substitute(arr(r, 1), " ", "")
  Next r
 
  'Write values back to sheet
  rng.Value = arr
 
  'Release object variables
  Set rng = Nothing
  Set ws = Nothing
 
End Sub

Actually this macro kind of works, here is the problem.

If account has leading zeros it deletes them and some of the accounts it makes them to the E power.