Trying to Copy a selected range, trim it and replace the range back where it came from

[tdvalentine44]

My end goal is to remove the spaces prior to and then replace the text back in the same column and row I got the text from. I am trying to select a column that I don't know if it will always be that column if I copy and paste from MS Project.

1. Select column
2. Using range function, copy that column into another column that won't be seen and perform the trim function during the copying process.
3. Paste the new text with the leading trailing spaces removed back in the original column.

Below is the start of my script. I've tried doing this and just end up with the program stopping or fail.

Sub TrimTaskName()
Application.ScreenUpdating = False
Dim ssheet As Worksheet

Set ssheet = ActiveSheet
LastRow = ssheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = ssheet.Cells(1, Columns.Count).End(xlToLeft).Column

'column for Task Name
For X = 1 To LastCol
If ssheet.Cells(1, X) = "Task Name" Then
TaskNameCol = X
GoTo NextStep
End If
Next X

NextStep:

row_number = 1
Do
DoEvents

row_number = row_number + 1
TestString = Cells(row_number, TaskNameCol)
TrimString = Application.Trim(TestString)
returnValue = TrimString.Trim()
Application.ScreenUpdating = True
Loop Until row_number = lr

End Sub

I've tried the record macro function and that work but trying to implement that after finding the "Task Name" column is proving difficult.

This was the recorded Macro.

ActiveCell.FormulaR1C1 = "=TRIM(RC[-7])"
Selection.AutoFill Destination:=Range("K2:K186"), Type:=xlFillDefault
Range("K2:K186").Select
ActiveWindow.SmallScroll Down:=-192
Selection.Copy
Range("D2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Thanks,
Todd

 

[JoeMo]

Does this do what you want?

Sub TrimTaskName()
Dim c As Range, colNum As Long
Const myCol As String = "Task Name"
On Error Resume Next
colNum = Cells.Find(myCol).Column
If Err.Number = 0 Then
    For Each c In Intersect(Columns(colNum), ActiveSheet.UsedRange)
        c.Value = WorksheetFunction.Trim(c.Value)
    Next c
Else
    MsgBox "Header ""Task Name"" not found"
End If
End Sub

 

[tdvalentine44]

Joe,

Thanks a lot. It is exactly what I am looking for. It worked great. Thank You!!!