CODEWITHSUNDEEP
excelvba
RFC
RFC

Reputation: 23

Using Application.WorksheetFunction.Trim to trim a range of cells

I'm trying to Trim a range of cells using the Application.WorksheetFunction.Trim. I'm trying to define my range, but I get a type mismatch error

I created Dim and set my range and created a variable for the function.

Dim rng As Range
Dim myanswer

Set rng = ActiveSheet.Range("T2:T10")
myanswer = Application.WorksheetFunction.Trim(rng)

Here's another one of my codes

Dim rng As Range
Dim LastRow As Long

LastRow = Cells(Rows.Count, "T").End(xlUp).Row
Set rng = Range("T2:T" & LastRow)

Application.WorksheetFunction.Trim (rng)

I want it to trim each of cells to get rid extra spaces.

Also tried using this but get out of memory.

rng = Application.WorksheetFunction.Trim(Cells.Value)

Upvotes: 1

Views: 4716

Answers (3)

Storax
Storax

Reputation: 12167

Although an old question there seems to be a real quick way to do this. The VBA.Trim, WorksheetFunction.Trim only accept strings as input but Application.Trim does accept a range as input.

So instead of Application.WorksheetFunction.Trim (rng) you just use Application.Trim (rng).

Upvotes: 4

SJR
SJR

Reputation: 23081

Your code doesn't work because you need to loop through each cell, you can't do them all in one go

Dim rng As Range, r as range
Dim myanswer

Set rng = ActiveSheet.Range("T2:T10")
for each r in rng    
     r.value=Application.WorksheetFunction.Trim(r)
next r

You can avoid a loop using Evaluate

Sub x()

Dim r As Range

Set r = Range("T2:T10")

r = Evaluate("IF(" & r.Address & "="""","""",TRIM(" & r.Address & "))")

End Sub

Note that the worksheet function TRIM removes superfluous internal spaces whereas VBA Trim only removes leading and trailing spaces (you had this covered anyway).

Upvotes: 4

Vityata
Vityata

Reputation: 43575

Trim() expects string argument. The argument in Application.WorksheetFunction.Trim (rng) is a Range, which can be parsed to a string, only if the range consists of a single cell.

To Trim() all cells in a given range, they should be looped:

Public Sub TestMe()

    Dim myCell As Range
    For Each myCell In Worksheets(1).Range("A1:A5")
        myCell = WorksheetFunction.Trim(myCell)
    Next myCell

End Sub

Upvotes: 2

Related Questions