Trim Help

[southcoast]

Hi all, I am a new member, don’t know if anyone can help. I am trying to find a “Formula” or VBA, (preferably a formula), to trim a complete"worksheet" in Mac Excel 2016, I am ok on a column, thats not a problem but when I have 12 columns some times 15 columns in a table/worksheet it would be nice to TRIM all in one go instead of 1 column at a time. I look forward to any reply, Regards, Roger.

 

[elpablo]

I have no experience on a Mac, but, here is a macro solution for you to consider on a PC, that does not do any looping thru all the cells in the used range of a worksheet.
...

Sub TrimMeV2()
' hiker95, 01/17/2017, ME986219
Dim lr As Long, lc As Long
Application.ScreenUpdating = False
With ActiveSheet
  lr = .Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
  lc = .Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
  With .Range(.Cells(1, 1), .Cells(lr, lc))
    .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
  End With
  .Columns(1).Resize(, lc).AutoFit
End With
Application.ScreenUpdating = True
End Sub

Wonderful!!!
A very great great thank you for this macro, hicker95 !
I've just registered on MrExcel just to thank you!
I owe you a bottle of good red wine!

 

[hiker95]

Wonderful!!!
A very great great thank you for this macro, hicker95 !
I've just registered on MrExcel just to thank you!
I owe you a bottle of good red wine!

elpablo,

Welcome to the MrExcel forum.

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.