Trim on Range Type

[fairgame]

Hi,

WorksheetFunction.CountIf(ws.Columns(2), ename)

ws.Columns(2) Range have some additional spaces, what is best way to apply trim on Columns(2), other than looping?

Thanks in Advance

 

[Jerry Sullivan]

Hi fairgame,

If your range is not greater than 65,536 cells, you can use the Application.Trim() function.

Sub Trim_Range() 
    Dim ws As Worksheet
    Set ws = Sheets("Sheet1")
    With ws
        With .Range("B1:B" & .Cells(.Rows.Count, 2).End(xlUp).Row)
            .Value = Application.Trim(.Value)
        End With
    End With
    Set ws = Nothing
End Sub