0
\$\begingroup\$

I have a VBA code sample that I use on a table to delete spaces in cells. I want to ask if someone has a better and quicker way to the Trim function on a table than what I have:

This is the sample:

If Sheets("Main").Cells(8, 6) = 1 Then
    strName = Sheets("Main").Cells(8, 3)
    Workbooks.Open Filename:="V:\á÷øåú ùëø\ãåçåú\ãåçåú çæåúééí\ãåçåú ìäîøä\exhr0101.csv"
    Columns(7).Delete
    Cells.Select
    Selection.Copy
    Windows("Trim all cells.xlsm").Activate
    Sheets("Data").Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    'ñâéøú çæåúé î÷åø
    Windows("exhr0101.csv").Activate
    Application.DisplayAlerts = False
    ActiveWindow.Close
    Application.DisplayAlerts = True
    'áéöåò Trim
    Windows("Trim all cells.xlsm").Activate
    Sheets("Data").Select
    For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
    cell = WorksheetFunction.Trim(cell)
    Next cell
    'äãá÷ä áâéìéåï äãá÷ä
    Cells.Select
    Selection.Copy
    Sheets("Target").Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Here is my specific trim:

For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
cell = WorksheetFunction.Trim(cell)
Next cell
\$\endgroup\$
7
  • \$\begingroup\$ For a start is there a typed function Trim$ ? \$\endgroup\$
    – QHarr
    Feb 18 '18 at 14:41
  • \$\begingroup\$ Application.Worksheetfunction.Trim() will leave a single space between words in a cell. Is this what you want?? \$\endgroup\$ Feb 18 '18 at 14:47
  • \$\begingroup\$ @QHarr what do you mean \$\endgroup\$ Feb 18 '18 at 14:47
  • \$\begingroup\$ @Gary'sStudent yes. I know what trim does. I want it to do it quicker \$\endgroup\$ Feb 18 '18 at 14:48
  • \$\begingroup\$ Can you replace Trim with Trim$ without raising an error or it being swopped back to Trim. I can’t remember. \$\endgroup\$
    – QHarr
    Feb 18 '18 at 14:51
3
\$\begingroup\$

You can avoid loop altogether.

 With Activesheet.UsedRange
     .Value = Application.Trim(.Value)
 End With

Warning: will give error if any cell has more than 255 characters.

\$\endgroup\$
0
\$\begingroup\$

You don't need to have the data in a specific worksheet to use Trim, or any macro for that matter. It looks like you are copying data from your worksheet to "Trim all cells.xlsm." In reality, if you just have the workbook open and make sure the active cell is on the workbook you want to trim, you should be good.

Also, setting Application.ScreenUpdating to false before you run the code will speed it up dramatically. Just make sure you set it back to true at the end of the code.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.