CODEWITHSUNDEEP
excelvba
122user321
122user321

Reputation: 231

Trim Cells in excel

I am trying remove the spaces from excel cell.the code what i am using is as fallows:

    Dim cell As Range, areaToTrim As Range
    Set areaToTrim = Sheet1.Range("A1:D50")
    For Each cell In areaToTrim
        cell.Value = LTrim(cell.Value)
    Next cell

The error what i get is as fallows:
enter image description here

Kindly give me better idea to trim the leading and trailing spaces present in the cell.

Upvotes: 0

Views: 477

Answers (1)

Skip Intro
Skip Intro

Reputation: 860

This will trim the cells, both leading and trailing.

lngCounter gets the last row of data, strRange gets the active column and sets the rows 2 to lngCounter, i.e. the last row of data.

The code walks around each Cell in strRange and applies Trim. This cuts all of the spaces from the cell value.

Sub CutMe()

Dim lngCounter As Long
Dim rCell As Range
Dim strRange As String

lngCounter = Cells.Find("*", [A1], , , xlByRows, xlPrevious).row 
strRange = Chr(ActiveCell.Column + 64) & "2:" & Chr(ActiveCell.Column + 64) & lngCounter

For Each rCell In Range(strRange).Cells

    rCell.Value = Trim(rCell.Value)

Next rCell

End Sub

Upvotes: 1

Related Questions