Type mismatch (Error 13) when using vba to trim values

Question

I have a data report that comes from another company. One of the fields I need to use has about 30 extra trailing spaces (I have no idea why) in each row. I only need to work with some of the columns, so what I've done is pick data from a column one at a time and drop it into a new sheet in an order that suits my analysis. I don't use copy & paste for this, I use worksheets("A").range("...").value = worksheets("B").range("...").value. This works to grab the columns I want and place them in the order I want. After reorganizing, I tried this to trim the cells in the column:

    Worksheets("B").Range("F2:F" & rw).Value = Trim(Worksheets("B").Range("F2:F" & rw).Value)

I've tried tweaking this line based on google searches, but every time I get a Type mismatch (Error 13) right here. rw is a variable I've set for the number of rows my data lives in. What am I doing wrong?

EDIT

I added a loop:

myArr = Worksheets("B").Range("F2:F" & rw).Value
For i = LBound(myArr) To UBound(myArr)
    myArr(i, 1) = Trim(myArr(i, 1))
Next i
Worksheets("B").Range("F2:F" & rw).Value = myArr

It does what I want now.

Answer 1

The reason you're getting Type mismatch (Error 13) is because Trim() only works with strings, but you're attempting to trim an array.

SOLUTION:

1. If you're assigning a string to a single cell, then:

   a. Concatenate the string variables you wish, then

   b. Trim() the string, and assign it to your cell

2. Otherwise, if you're copying a collection of strings from one range to another range, then

   a. Do as BigBen suggests, and use a loop