Code to remove redundant spaces ?

[Maverick27]

Hi - does anyone know how i can get rid off SPACE after text in the column ?
Code or any function that i can run across the col ?
See "Red lines" in the link below.
http://imageshack.us/photo/my-images/703/excelh.jpg/

Thanks

 

[Richard Schollar]

Hi

You can probably use the Trim worksheet function (although this will remove leading spaces too, and multiple instances of 'interior' spaces). Code-wise this could look like:

For Each cell In Intersect(Activecell.EntireColumn,Activesheet.UsedRange)
  If Not IsEmpty(cell) Then _
      cell.Value = RTrim(cell.Value)
Next cell

The above only removes trailing spaces.

 

[Richard Schollar]

Hi

You can probably use the Trim worksheet function (although this will remove leading spaces too, and multiple instances of 'interior' spaces). Code-wise this could look like:

For Each cell In Intersect(Activecell.EntireColumn,Activesheet.UsedRange)
  If Not IsEmpty(cell) Then _
      cell.Value = RTrim(cell.Value)
Next cell

The above only removes trailing spaces.

 

[VoG]

Try selecting the cells then running the TrimAll macro http://dmcritchie.mvps.org/excel/join.htm

 

[Maverick27]

Hi

You can probably use the Trim worksheet function (although this will remove leading spaces too, and multiple instances of 'interior' spaces). Code-wise this could look like:

For Each cell In Intersect(Activecell.EntireColumn,Activesheet.UsedRange)
  If Not IsEmpty(cell) Then _
      cell.Value = RTrim(cell.Value)
Next cell

The above only removes trailing spaces.

Thanks - can you modify the code so that it can be applied to COL.D of Excel sheet ? Code is what i want as i only want to get rid off of trailing spaces.

 

[Richard Schollar]

Sure:

For Each cell In Intersect(ActiveSheet.Range("D:D"),Activesheet.UsedRange)
  If Not IsEmpty(cell) Then _
      cell.Value = RTrim(cell.Value)
Next cell

 

[Maverick27]

Sure:

For Each cell In Intersect(ActiveSheet.Range("D:D"),Activesheet.UsedRange)
  If Not IsEmpty(cell) Then _
      cell.Value = RTrim(cell.Value)
Next cell

Stupid qtn - where do i put the code ?
Right Click on worksheet> View Code & paste ?

 

[Maverick27]

Somebody pls help with code.
i did ALT F11, insert module to paste the code & alt Q to close. Run compile. giving compilation error. Havr i gone wrong somewhere ?

 

[GTO]

Does it give a description of teh error?

 

[Maverick27]

Does it give a description of teh error?

After pasting the code, opens up Macor Box, i select the only availalbe option in Macro then Error : Invalid outside procedure