remove spaces from range vba code example
Example 1: excel vba clean extra spaces from an entire range
Function CleanMAX(r As Range)
CleanMAX = Replace("trim(clean(substitute(|,char(160),"" "")))", "|", r.Address)
If r.Cells.Count > 1 Then CleanMAX = "index(" & CleanMAX & ",)"
CleanMAX = Evaluate(CleanMAX)
End Function
v = CleanMAX([a1])
v = CleanMAX([a1:z10])
Example 2: vba remove spaces
Dim s As String
s = " abc "
Debug.Print "|" & LTrim(s) & "|"
Debug.Print "|" & RTrim(s) & "|"
Debug.Print "|" & Trim(s) & "|"