0

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"

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Simone Evans
  • 183
  • 2
  • 17
  • Not too sure but try: `.Address(ColumnAbsolute:=False)` – Dean Jul 09 '19 at 10:15
  • @Dean unfortunately, I still get the same wrong replacement in the headings – Simone Evans Jul 09 '19 at 10:20
  • 2
    Your code works for me... Are you sure those are actually spaces and not non printable characters? – Siddharth Rout Jul 09 '19 at 10:23
  • I cannot reproduce your issue. The code worked perfectly. • Note that *"didn't work"* is not an error description. Which errors do you get? What happens? Is there a `On Error Resume Next` in your code? – Pᴇʜ Jul 09 '19 at 10:24
  • 1
    @SiddharthRout they are definitely spaces. As mentioned above the code works on it's own or if I run the macro in breaks, but not if I run the whole long macro. I've edit my question with an update. It seems It's replacing the headings with whichever cell and sheet is selected. – Simone Evans Jul 09 '19 at 10:26
  • 1
    In that case I need to see the complete macro. You maybe resetting the range somewhere in the code – Siddharth Rout Jul 09 '19 at 10:27
  • @Pᴇʜ The code I used for that doesn't work because it's an array is the following: With wsDispoData.ListObjects("Table_DispoData").HeaderRowRange.EntireRow .Value = WorksheetFunction.Trim(.Value) End With. It gives an error "Run-time error 13: type mismatch" – Simone Evans Jul 09 '19 at 10:32
  • @SiddharthRout It is a very very long macro, and I use this trim code on 4 different tables. I tried running the code and stop it just before it starts on the second table, but it changes the headings even before it gets to that point. Should I post a section of the code? – Simone Evans Jul 09 '19 at 10:40
  • `.Value = WorksheetFunction.Trim(.Value)` is wrong as it applies to `.EntireRow` so it refers to all cell in the entire row (16K columns) while `Trim` inputs a single value. What happens if you omit `.EntireRow`? – AcsErno Jul 09 '19 at 11:44
  • @AcsErno I'm using the `Evaluate` function instead of the `WorksheetFunction.Trim` because the range is an array which the `Trim` function doesn't work with since it's not a single cell or value. I've tried to remove the `.EntireRow` just to test and I get the error "Run-time error 13" Type-mismatch" as mentioned in the post – Simone Evans Jul 09 '19 at 12:00
  • 1
    @Simone Fick Yes, that was a quick and wrong advise. To take it seriously: Type mismatch by omitting entirerow is correct as headerrowrange returns a range, not a string. So you might try `For Each c in wsDispoData.ListObjects("Table_DispoData").HeaderRowRange: c.Value = Trim(c.Value) : Next` – AcsErno Jul 09 '19 at 12:16
  • @AcsErno Thought I would get away from doing a loop, but it works perfectly thank you! – Simone Evans Jul 09 '19 at 14:46

1 Answers1

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
Tragamor
  • 3,594
  • 3
  • 15
  • 32