vba remove spaces
Dim s As String
s = " abc "
Debug.Print "|" & LTrim(s) & "|" ' |abc |
Debug.Print "|" & RTrim(s) & "|" ' | abc|
Debug.Print "|" & Trim(s) & "|" ' |abc|vba remove spaces
Dim s As String
s = " abc "
Debug.Print "|" & LTrim(s) & "|" ' |abc |
Debug.Print "|" & RTrim(s) & "|" ' | abc|
Debug.Print "|" & Trim(s) & "|" ' |abc|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 SubCopyright © 2021 Codeinu
Forgot your account's password or having trouble logging into your Account? Don't worry, we'll help you to get back your account. Enter your email address and we'll send you a recovery link to reset your password. If you are experiencing problems resetting your password contact us
