VBA TRIM Column

[Jeffreyxx01]

Hi guys,

can someone help to fix my macro?
Thanks,

Sub Trim_column()
 
Dim LastRow As Long
Dim Sht as Worksheet
 
Set Sht = ActiveWorkbook.Sheets("DataBase")
 
With Sht
LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("A2:A" & LastRow).value = Trim(Range(“A2:A” & LastRow).Value)
      
End With
 
End Sub

 

[Fluff]

How about

Sub MyTrim()
With Range("A1", Range("A" & Rows.Count).End(xlUp))
   .Value = Evaluate("if({1},trim(" & .Address & "))")
End With
End Sub

 

[Jeffreyxx01]

Thanks Fluff, seems to work, but what about a Trim that would do column C D E F G H ?

 

[Fluff]

Are all the columns the length?

 

[Jeffreyxx01]

Well I just need the column C D E F G H, that it finds the last rows and still apply for trimming.

 

[Fluff]

Yes, but are all those columns the same length?

 

[Jeffreyxx01]

What do you mean the same length? They all have a string in the cell.

 

[Fluff]

Is the last used row in each column the same?

 

[Jeffreyxx01]

No it is not the same, some cells contain 2, 3 words, and other contain just 1 word.

 

[Fluff]

How about

Sub MyTrim()
   Dim lr As Long
   lr = Cells.find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row
   With Range("A1:H" & lr)
      .Value = Evaluate("if({1},trim(" & .Address & "))")
   End With
End Sub

This will trim col B as well if that's not a problem