How to execute a function for each cell in a column and loop through all workbooks?

user2510323 picture user2510323 · Jun 21, 2013 · Viewed 75.3k times · Source

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.

Answer

Kazimierz Jawor picture Kazimierz Jawor · Jun 21, 2013

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.