visual basic excel remove spaces
' NOTE: this function is for Microsoft Excel only (language: VBA)
'===============================================================================
'>> RemoveSpaceCharsInSelectedCells()
'===============================================================================
' Removes the spaces in the selected cells
'===============================================================================
Sub RemoveSpaceCharsInSelectedCells()
Dim sFunct As String: sFunct = "RemoveSpaceCharsInSelectedCells"
Dim bDebugging As Boolean: bDebugging = False
If (bDebugging = True) Then
Debug.Print Format(DateTime.Now, "hh:mm:ss") & " INFO " & sFunct & "| " _
& "Running.. [Cells:" & Selection.Address & "]"
End If
'*********************************
' VALIDATIONS and declarations
'*********************************
'(DECLARATIONS)
Dim wbInit As Workbook: Set wbInit = ActiveWorkbook
Dim wsInit As Worksheet: Set wsInit = ActiveSheet
Dim s_rInit As String: s_rInit = Selection.Address
Dim sErrMsg As String
Dim sReplaceRng As String
Dim sReplaceString As String
Dim sNewString As String
'(SETTINGS/SETUP)
Application.ScreenUpdating = False
sReplaceRng = Selection.Address
' sReplaceString = " "
sNewString = ""
'---------------------------------
' WORK
'---------------------------------
'1) Replace the relevant cells containing the sReplaceString
' a) Remove the normal space value (ascii: 32)
' b) Remove the html space value (ascii: 160)
' c) Remove the html space value (ascii: nbsp)
'Z) Reactivate the initial workbook/worksheet
'--(1.a)
sReplaceString = Chr(32) ' normal space (ascii: 32)
Selection.Replace What:=sReplaceString, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
'--(1.b)
sReplaceString = Chr(160) ' html number's space (ascii: 160)
Selection.Replace What:=sReplaceString, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
'--(1.c)
sReplaceString = Chr(nbsp) ' html name's space (ascii: 160)
Selection.Replace What:=sReplaceString, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
'--(Z)
wbInit.Activate
wsInit.Activate
Range(s_rInit).Select
'-----------v-----------DEBUG INFO-----------v-----------
If (bDebugging = True) Then
Debug.Print Format(DateTime.Now, "hh:mm:ss") & " INFO " & sFunct & "| " _
& "Complete for cells [" & Selection.Address & "]"
End If
Application.ScreenUpdating = True
End Sub