How to remove empty space, unable to make pvot, because pvot calculate empty space

Perhaps a macro like this to "trim" column B?

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("B" & i)
        .Value = WorksheetFunction.Trim(.Value)
    End With
Next i
End Sub

Alf