You can add the following subroutine. It'll apply the formula to all used
cell which are found to be non-empty.

Sub apply_to_all_cells()
Dim cell As Object
With ActiveSheet.UsedRange
For Each cell In .Cells
If cell.Value < "" Then
cell.Value = TrimSpace(cell.Value)
End If
Next
End With
End Function


"thanhnguyen" wrote:


Hi all member!

I found this function to trim 2 spaces not neccessary in the string :

Example: TrimSpace(" This is new user ") ="This is new user"

How can i use this function to apply to the whole worksheet?



Function TrimSpace(strInput As String) As String
' This procedure trims extra space from any part of a string

Dim astrInput() As String
Dim astrText() As String
Dim strElement As String
Dim lngCount As Long
Dim lngIncr As Long
If Trim(strInput) = "" Then Exit Function
' Split passed-in string.
astrInput = Split(Trim(strInput))

' Resize second array to be same size.
ReDim astrText(UBound(astrInput))

' Initialize counter variable for second array.

lngIncr = LBound(astrInput)
' Loop through split array, looking for
' non-zero-length strings.
For lngCount = LBound(astrInput) To UBound(astrInput)
strElement = astrInput(lngCount)
If Len(strElement) 0 Then
' Store in second array.
astrText(lngIncr) = strElement
lngIncr = lngIncr + 1
End If
Next
' Resize new array.
ReDim Preserve astrText(LBound(astrText) To lngIncr - 1)

' Join new array to return string.
TrimSpace = Join(astrText)
End Function


--
thanhnguyen