Removing Extra Spaces At The End Of Range Of Varying Cells

[milwphil]

Once again…… I’m stuck. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I’m trying to find a method of getting rid of unintentional extra space(s) at the end of range of cells (C11: Z –last row of data). I’ve found several discussions on how to use the Trim() function, but nothing that has worked for my situation. I think the problem I’m having is due to protected fields. <o></o>
<o></o>
My diagnosis is that the range is messed up (sorry for not being more technical than that). <o></o>
<o></o>
The other problem is that I need to have is that above the header row…I have a couple cells that need the same Trim() function (E5:E8).<o></o>
<o></o>
Any help someone could provide…would be GREATLY appreciated!<o></o>
<o></o>
Thanks,<o></o>
Phil <o></o>

 

[T. Valko]

Once again…… I’m stuck. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I’m trying to find a method of getting rid of unintentional extra space(s) at the end of range of cells (C11: Z –last row of data). I’ve found several discussions on how to use the Trim() function, but nothing that has worked for my situation. I think the problem I’m having is due to protected fields. <o></o>
<o></o>
My diagnosis is that the range is messed up (sorry for not being more technical than that). <o></o>
<o></o>
The other problem is that I need to have is that above the header row…I have a couple cells that need the same Trim() function (E5:E8).<o></o>
<o></o>
Any help someone could provide…would be GREATLY appreciated!<o></o>
<o></o>
Thanks,<o></o>
Phil <o></o>

The macro at this website will remove all leading/trailing and multiple interspersed char 32 space characters. It will also remove and/or convert char 160 non breaking spaces into standard char 32 space characters. It will work on text or numbers and the numbers will be converted to true numeric numbers.

I use this macro dozens of times every single day! It's a real time saver.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

 

[milwphil]

Thanks for the link!

I see how to use the code in a basic form-as it is provided, but I need to have a defined range rather than a Selection. I'm not sure how to do that.

How can I manipulate the code to define the range. The problem I have is that the range will have a LastRow that may be different every time a user uses the file.

Sorry for being picky!

 

[milwphil]

This seems to work too, but I just can't figure out how to define my range...

Sub KillSupSpaces()
Dim rCell As Range
Dim strText As String
Dim lCount As Long        
 
With WorksheetFunction            
For Each rCell In Selection                
strText = rCell                
rCell = .Trim(.Clean(rCell))                 
If rCell <> strText Then lCount = lCount + 1            
Next rCell        
End With                        
MsgBox lCount & " cells had superfluous spacing"
 
End Sub
 
 


</PRE>

 

[T. Valko]

Thanks for the link!

I see how to use the code in a basic form-as it is provided, but I need to have a defined range rather than a Selection. I'm not sure how to do that.

How can I manipulate the code to define the range. The problem I have is that the range will have a LastRow that may be different every time a user uses the file.

Sorry for being picky!

I'm not much of a programmer.

I'll bump this thread and maybe someone will see it and offer a better solution. Good luck!