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.

 

[My Aswer Is This]

Try this:

Sub Trim_Me()
Application.ScreenUpdating = False
Dim c As Range
    For Each c In ActiveSheet.UsedRange
        If c.Value <> "" Then c.Value = Trim(c.Value)
    Next
End Sub

Works on PC not sure about Mac

 

[Michael M]

Don't know about Mac but try...

Sub SelectUsedRange()
For Each cell In ActiveSheet.UsedRange
cell.Value = Trim(cell.Value)
Next cell
End Sub

 

[JoeMo]

If your range is very large, this may be faster. Select the entire range you want to trim with your mouse when prompted.

Sub TrimAllCells()
Dim R As Range, v As Variant
Dim i As Long, j As Long, ct As Long
On Error Resume Next
Set R = Application.InputBox("Select the range you want to trim", Type:=8)
On Error GoTo 0
If R Is Nothing Then Exit Sub
v = R.Value
For i = 1 To UBound(v, 1)
       For j = 1 To UBound(v, 2)
              ct = ct + 1
              v(i, j) = WorksheetFunction.Trim(v(i, j))
       Next j
Next i
MsgBox ct & " cells in the range " & R.Address(0, 0) & " have been trimmed"
End Sub

 

[hiker95]

southcoast,

Welcome to the MrExcel forum.

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.

Sample raw data in the active worksheet:

Excel 2007
	A	B	C
1	a a	b b	c c
2	a a	b b	c c
3	a a	b b	c c
4	a a	b b	c c
5	a a	b b	c c
6	a a	b b	c c
7	a a	b b	c c
8	a a	b b	c c
9	a a	b b	c c
10	a a	b b	c c
11			c c
12			c c
13			c c
14			c c
15			c c
16			c c
17
Sheet1

And, after the macro:

Excel 2007
	A	B	C
1	a a	b b	c c
2	a a	b b	c c
3	a a	b b	c c
4	a a	b b	c c
5	a a	b b	c c
6	a a	b b	c c
7	a a	b b	c c
8	a a	b b	c c
9	a a	b b	c c
10	a a	b b	c c
11			c c
12			c c
13			c c
14			c c
15			c c
16			c c
17
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

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

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the TrimMeV2 macro.

 

[southcoast]

Hi, Thanks to all for the reply's and the help, they have helped me a great deal, just got to learn more about Macros now, Regards, Roger

 

[decadence]

Although it's already answered here is what I use

Sub Trim()
'
' Removes all leading and trailing spaces
'
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    ActiveSheet.Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select
    Selection = Evaluate("index(clean(trim(" & Selection.Address & ")),)")
    Selection.Replace What:=Chr(160), Replacement:=Chr(32), LookAt:=xlPart, SearchOrder:=xlByRows
    Dim cell As Range
    On Error Resume Next
    Application.ScreenUpdating = False
    For Each cell In Intersect(Selection, Selection.SpecialCells(xlConstants, xlTextValues))
        cell.Value = Application.Trim(cell.Value)
        cell.Value = Application.WorksheetFunction.Clean(cell.Value)
    Next cell
    On Error GoTo 0
    Application.Calculation = xlCalculationAutomatic
    End With
    Range("A1").Select
    Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

Glad to see a half dozen different answers here. I will move on and find a posting which no one has provided a answer for.

Hi, Thanks to all for the reply's and the help, they have helped me a great deal, just got to learn more about Macros now, Regards, Roger

 

[hiker95]

Re: Trim Help
Hi, Thanks to all for the reply's and the help, they have helped me a great deal, just got to learn more about Macros now, Regards, Roger

southcoast,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.

 

[hiker95]

just got to learn more about Macros now

southcoast,

See if something, in the below link(s), of my most up to date list will help you:

Training / Books / Sites as of 01/03/2017

http://www.mrexcel.com/forum/excel-...rn-visual-basic-applications.html#post4723068