Trim Cells using VBA in Excel

Question

I have what seems like a simple problem with some data in Excel  I have a lot of data with leading spaces pasted from a web table  and I would like to get rid of the initial space  I cribbed the following code  I am completely new to VBA   but it doesn t seem to work  When I step through it in the debugger it looks like an infinite loop  Any help would be appreciated   Sub DoTrim     For Each cell In Selection Cells     If cell HasFormula   False Then       cell   Trim cell      End If   Next End Sub   EDIT  It does look like the TRIM function is running into problems with a  space  character  This code   Sub DoTrim   Dim cell As Range  areaToTrim As Range Set areaToTrim   Selection Cells For Each cell In areaToTrim     cell Value    MUPPET  Next cell End Sub   Changed the value of the cells  so I guess it s a non-space whitespace  Any idea on how to get rid of those

User · Answer

I would try to solve this without VBA  Just select this space and use replace  change to nothing  on that worksheet you re trying to get rid off those spaces   If you really want to use VBA I believe you could select first character  strSpace   left range  A1   Value 1    and use replace function in VBA the same way  Range  A1   Value   Replace Range  A1   Value  strSpace        or  for each cell in selection cells  cell value   replace cell value  strSpace      next

User · Answer

This should accomplish what you want to do   I just tested it on a sheet of mine  let me know if this doesn t work   LTrim is if you only have leading spaces  the Trim function can be used as well as it takes care of leading and trailing spaces   Replace the range of cells in the area I have as  A1 C50  and also make sure to change  Sheet1  to the name of the sheet you re working on     Dim cell As Range  areaToTrim As Range Set areaToTrim   Sheet1 Range  A1 C50   For Each cell In areaToTrim     cell Value   LTrim cell Value  Next cell

User · Answer

The infinite loop is a result of an On Error Resume Next statement somewhere higher up in your code  Get rid of it now  If your code only runs with On Error Resume Next in effect  it needs instant and complete overhaul    And while you are at it  place an Option Explicit on top of yor module  It forces you to declare all variables  a safeguard against annoying bugs that are the result of mis-typed variable names   You can modify the VBA editor preferences to have that option set automatically the next time  which is highly recommended    The immediate problem with your code is that a cell is an object  and objects cannot be trimmed  You want to trim the text of the cell  so you must do so   cell Text   Trim cell Text

User · Answer

If you have a non-printing character at the front of the string try this   Option Explicit Sub DoTrim       Dim cell As Range     Dim str As String     Dim nAscii As Integer     For Each cell In Selection Cells         If cell HasFormula   False Then             str   Trim CStr cell               If Len str    0 Then                 nAscii   Asc Left str  1                   If nAscii  lt 33 Or nAscii   160 Then                     If Len str   gt 1 Then                         str   Right str  Len str  - 1                      Else                         strl                          End If                 End If             End If             cell str         End If     Next End Sub

User · Answer

Only thing that worked for me is this function   Sub DoTrim   Dim cell As Range Dim str As String For Each cell In Selection Cells     If cell HasFormula   False Then         str   Left cell Value  1   space         While str       Or str   Chr 160              cell Value   Right cell Value  Len cell Value  - 1              str   Left cell Value  1   space         Wend         str   Right cell Value  1   space         While str       Or str   Chr 160              cell Value   Left cell Value  Len cell Value  - 1              str   Right cell Value  1   space         Wend     End If Next cell End Sub

User · Answer

Worked for me perfectly as this    Trims all selected cells  Beware of selecting full columns rows  P   Sub TrimSelected       Dim rng As Range  cell As Range     Set rng   Selection     For Each cell In rng     cell   Trim cell      Next cell      End Sub

User · Answer

This works well for me  It uses an array so you aren t looping through each cell  Runs much faster over large worksheet sections   Sub Trim Cells Array Method    Dim arrData   As Variant Dim arrReturnData   As Variant Dim rng As Excel Range Dim lRows As Long Dim lCols As Long Dim i As Long  j As Long    lRows   Selection Rows count   lCols   Selection Columns count    ReDim arrData 1 To lRows  1 To lCols    ReDim arrReturnData 1 To lRows  1 To lCols     Set rng   Selection   arrData   rng value    For j   1 To lCols     For i   1 To lRows       arrReturnData i  j    Trim arrData i  j       Next i   Next j    rng value   arrReturnData    Set rng   Nothing End Sub

User · Answer

Works fine for me with one change - fourth line should be   cell Value   Trim cell Value      Edit  If it appears to be stuck in a loop  I d add   Debug Print cell Address   inside your For     Next loop to get a bit more info on what s happening   I also suspect that Trim only strips spaces - are you sure you haven t got some other kind of non-display character in there

[vba] Trim Cells using VBA in Excel

Examples related to vba

Examples related to excel