10

Here's what I have so far:

Sub TrimColumnD()
   Dim ws As Worksheet

   For Each ws In ThisWorkbook.Worksheets
   Dim c As Range
        For Each c In ActiveSheet.UsedRange.Columns("D").Cells
            c.Value = WorksheetFunction.Trim(c.Value)
        Next c
   Next ws

End Sub

The trim function only works on the cells in the first worksheet.

Community
  • 1
  • 1
user2510323
  • 101
  • 1
  • 1
  • 3
  • 2
    Make sure you format your code clearly with proper indents. It's also a good idea to declare your variable together at the top of the sub. – Jon Crowell Jun 21 '13 at 20:56

1 Answers1

15

Please change this line:

For Each c In ActiveSheet.UsedRange.Columns("D").Cells

into this one:

For Each c In ws.UsedRange.Columns("D").Cells

In your code internal loop refers to activesheet while it should refer to ws variable representing sheet.

Kazimierz Jawor
  • 18,861
  • 7
  • 35
  • 55