How to Trim Headings in a table with VBA code

Question

I'm trying to trim the headings of a table with VBA code because some of them have spaces in front that varies every month, which makes it difficult for coding.

When I break down the code and run it step by step it works fine but when I run the whole macro it removes some of my headings and replace them with info from a different sheet row or just "column 1", "column 2", etc.

I believe I'm missing some code reference when it calls the (" & .Address & ") selection?

It's replacing the headings from a sheet where the cell is active. (If it was the last cell to click on before running the macro).

I've tried just using the Trim function, but because it's an array for the range, it doesn't work, and someone suggested to use the "evaluate" function.

I've tried using the trim function as a WorksheetFunction as well but it gave me an error "Run-time error 13" Type-mismatch". Which was on the following code:

    With wsDispoData.ListObjects("Table_DispoData").HeaderRowRange.EntireRow
        .Value = WorksheetFunction.Trim(.Value)
    End With

This is the current code I'm using that replaces wrongly.

Trim headings
    With wsDispoData.ListObjects("Table_DispoData").HeaderRowRange.EntireRow
        .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
    End With

Expected results should be for example:

Current headings: " SOH" and " Compo"

Trimmed: "SOH" and "Compo"

Answer 1

I would just enumerate through the header row and check each value

Dim Cell As Range
For Each Cell In wsDispoData.ListObjects("Table_DispoData").HeaderRowRange
    Cell.value = WorksheetFunction.Trim(Cell.value)
Next Cell