TRIM using VBA

[venugosr]

Hi Friends,

Good Morning!!

I need your help on the below.

In the Sheet1, Column A, I have the "N" of rows. Consider 24,000 everytime.
In that few has space in the cell.
Example:
"College ", but it should be "College" (without space).
Now i am manually copy all the data's in Sheet2 and using the trim function and again copy in the sheet1.

Someone please help me to automate this.

Note:

I have the data's in the column A only in sheet1.

Thanks.

 

[mancubus]

Sub CleanCells()

    Dim cll As Range

    For Each cll In Worksheets("Sheet1").Columns(1).SpecialCells(xlCellTypeConstants, 23)
        cll = CleanTrim(cll)
    Next cll

End Sub

uses the following UDF:

Function CleanTrim(ByVal StrToClean As String, Optional ConvertNonBreakingSpace As Boolean = True) As String
'http://www.excelfox.com/forum/showthread.php/155-Trim-all-Cells-in-a-Worksheet-VBA

  Dim j As Long
  Dim CodesToClean As Variant
  
  CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
                       21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
  
  If ConvertNonBreakingSpace Then StrToClean = Replace(StrToClean, Chr(160), " ")
  
  For j = LBound(CodesToClean) To UBound(CodesToClean)
    If InStr(StrToClean, Chr(CodesToClean(j))) Then StrToClean = Replace(StrToClean, Chr(CodesToClean(j)), "")
  Next j
  
  CleanTrim = WorksheetFunction.Trim(StrToClean)

End Function

 

[MARK858]

Try

Sub TRIMSample()
    [A1:A24000] = [INDEX(TRIM(A1:A240000),)]
End Sub

 

[TotallyConfused]

The code below written by Mark858 works great.

Try

Sub TRIMSample()
    [A1:A24000] = [INDEX(TRIM(A1:A240000),)]
End Sub

You might also try this simplified version of his code which seems to work also.

Sub TRIMSample()
    [A1:A24000] = [TRIM(A1:A240000)]
End Sub

 

[venugosr]

The problem is, the nows is not fixed.. It will keep on change.

 

[MARK858]

Then make the top number past what you expect the maximum number of rows to be, it makes no real difference if some are empty.