CODEWITHSUNDEEP
excelvbaexcel-formula
Migs
Migs

Reputation: 3

Remove/Trim spaces from cells in excel sheet using vba

I am trying to remove the leading and trailing spaces in an excel sheet. I have a code that is currently doing that but it is running through the entire sheet. I would like to select a range, instead of the whole sheet to save time.

I tried using a code already but i need to simplify to make the process faster.

Dim r As Range
For Each r In ActiveSheet.UsedRange
v = r.Value
If v <> "" Then
If Not r.HasFormula Then
r.Value = Trim(v)
End If
End If
Next r

This code will remove spaces from the entire sheet.

Upvotes: 0

Views: 1113

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60414

perhaps:

Option Explicit
Sub trimConstants()
    Dim R As Range, C As Range
    Dim WS As Worksheet

Set WS = Worksheets("sheet2") 'better than using `ActiveSheet`
Set R = WS.Cells.SpecialCells(xlCellTypeConstants)

For Each C In R
    C = Trim(C)
Next C

End Sub

Upvotes: 2

Related Questions