Counting the number of blanks removed by trim

[rob737]

Hi Forum

Hope all is well, would like a bit of help with a macro that trims spaces from cells.

I found this snippet and works great, however I would like to count the number of blanks the macro removes.

Sub NoSpaces()
Dim c As Range
For Each c In Selection.Cells
c = Trim(c)
Next
End Sub
​

As always would appreciate any help you can provide

Many thanks
Regards
Rob

 

[rollis13]

You could check the length of the string in "c" before and after trimming.

 

[Rick Rothstein]

Hi Forum

Hope all is well, would like a bit of help with a macro that trims spaces from cells.

I found this snippet and works great, however I would like to count the number of blanks the macro removes.

Sub NoSpaces()
  Dim c As Range[B][COLOR="#FF0000"], TotalLen As Long, TrimCount As Long[/COLOR][/B]
  For Each c In Selection.Cells
    [B][COLOR="#FF0000"]TotalLen = Len(c)[/COLOR][/B]
    c = Trim(c)
    [B][COLOR="#FF0000"]TrimCount = TotalLen - Len(c)[/COLOR][/B]
    [B][COLOR="#FF0000"]MsgBox "Number of characters trimmed: " & TrimCount[/COLOR][/B]
  Next
End Sub

Try adding the lines of code I show above.

 

[rob737]

Try adding the lines of code I show above.

Hi Rick

Great many thanks, the code counts the number of blanks removed for each cell which is great, how can I adjust it to get one messagebox for a total of all the cells as a total from the sheet.

As always any help would be greatly appreciated

Thanks

 

[L. Howard]

Maybe something like this.
And for a quickie test use cells B1:B5 as a "selected" test range with C1 & C2 = lens before and after and column D & E show before and after for each row.

Howard

Option Explicit

Sub NoSpaces()
 Dim c As Range
 
 For Each c In Selection.Cells
 
   [C1] = [C1] + Len(c)
   c.Offset(, 2) = Len(c)
   
   c = Trim(c)
 
   c.Offset(, 3) = Len(c)
   [C2] = [C2] + Len(c)
   
 Next
 
 MsgBox [C1] - [C2] & " Spaces removed"
 
 End Sub