Trim column in table databodyrange?

[cizzett]

So as discovered earlier I have an issue with extra spaces when I bring in data from another souce file.

What I want is to be able to, using VBA script, trim all extra spaces, specifically after the text.

My data is in a table titled "PasteTable" I have tried every solution I can think of and I can find on the web but cant seem to get it right.

The data length is dynamic, every time I use it it could be one row or 80 not telling.

Help, Suggestions, or guidance would be greatly appreciated.

 

[igold]

How about this...

Sub CleanTable()


    Dim r As Long, c As Long
    
    For r = 1 To ActiveSheet.ListObjects("PasteTable").ListRows.Count
        For c = 1 To ActiveSheet.ListObjects("PasteTable").ListColumns.Count
            ActiveSheet.ListObjects("PasteTable").DataBodyRange(r, c) = Trim(ActiveSheet.ListObjects("PasteTable").DataBodyRange(r, c))
        Next
    Next
    
End Sub

 

[cizzett]

I actually just got it to work, dont know why it kicked my but so bad I guess I was overthinking it.

Private Sub TrimDest()

Dim Cell As Range
  For Each Cell In ActiveSheet.UsedRange.Columns("D").Cells
    Cell.Value = WorksheetFunction.Trim(Cell.Value)
  Next
End Sub