CODEWITHSUNDEEP
excelvbamacos
PhilNBlanks
PhilNBlanks

Reputation: 125

Cannot figure out why I'm getting the following error: "Method 'Value' of object 'Range' failed"

I'm trying to the trim the blanks from all cells of a column using the following Sub but when I go to write the contents of the array back to my spreadsheet I keep getting the following error:

enter image description here

Here is my sub:

Sub TrimIDCUST()
    Dim wks As Worksheet
          Set wks = ShCust            'Work in sheet("shCust")
          Dim LastRow As Long
            ' Find last row shCust
            LastRow = ShCust.Range("A" & Rows.Count).End(xlUp).row
            
     Dim i As Long
     Dim CustID() As Variant
          CustID = Application.Transpose(wks.Range("A2:A" & LastRow).Value)
     
     For i = LBound(CustID) To UBound(CustID)
          CustID(i) = Application.WorksheetFunction.Trim(CustID(i))
     Next i
     wks.Range("A2:A" & LastRow).Value = Application.Transpose(CustID)
End Sub

I have used this basic framework successfully before and am stumped. Any help will be greatly appreciated.

If it matters Im using Office 365 on a Mac.

Upvotes: 0

Views: 79

Answers (1)

BigBen
BigBen

Reputation: 49998

You don't need an array or a loop to do this. Use the late-bound Application.Trim (Application.WorksheetFunction.Trim is early-bound).

With wks.Range("A2:A" & LastRow)
   .Value = Application.Trim(.Value)
End With

As mentioned in comments, you don't need Transpose and it can cause issues. If you're going the long way, simply work with the 2D Variant array and skip transposing.

CustID = wks.Range("A2:A" & LastRow).Value

For i = LBound(CustID, 1) To UBound(CustID, 1)
    CustID(i, 1) = Application.WorksheetFunction.Trim(CustID(i, 1))
Next i

wks.Range("A2:A" & LastRow).Value = CustID

Upvotes: 1

Related Questions