Removing Zeros From Multiple Cells (with LastRow)

[milwphil]

Good afternoon!

I'm stuck....ONCE AGAIN..... I have a spreadsheet that I need to build in the functionality to remove "end of character" blank spaces. For example, if someone enters "Excel " and adds blank spaces at the end rather than "Excel".

The worksheet that I have has a 'validate' macro that users need to run before they can sumbit the data. The validation essentially looks for errors in the worksheet. What I'd like to do is add VB code within the validation macro to remove the "end of character" blank spaces to all cells in the range.

The column range I have is C11:AG11. The problem is that the last row will vary pending how much data the user inputs.

Does anyone have a quick line a code that I could add into my validation macro to accomplish removing "end of character" blank spaces?

Any help you can provide is greatly appreciated!

Phil

 

[pboltonchina]

Try this on a copy of your data

Option Explicit

Sub TrimAll()
   'David McRitchie 2000-07-03 mod 2000-08-16 2005-09-29 join.htm
   '-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall
   ' - Optionally reenable improperly terminated Change Event macros
      Application.DisplayAlerts = True
      Application.EnableEvents = True   'should be part of Change Event macro
   If Application.Calculation = xlCalculationManual Then
      MsgBox "Calculation was OFF will be turned ON upon completion"
   End If
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim cell As Range
   'Also Treat CHR 0160, as a space (CHR 032)
   Selection.Replace what:=Chr(160), Replacement:=Chr(32), _
     LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   'Trim in Excel removes extra internal spaces, VBA does not
   On Error Resume Next   'in case no text cells in selection
   For Each cell In Intersect(Selection, _
      Selection.SpecialCells(xlConstants, xlTextValues))
     cell.Value = Application.Trim(cell.Value)
   Next cell
   On Error GoTo 0
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub

 

[milwphil]

I didn't get any errors but it's not taking away the spaces. Suggestions?

 

[pboltonchina]

It works for me. You could always try use ASAP Utilities, this has the function under 'Text' to remove leading, trailing and excessive spaces.

http://www.asap-utilities.com