Remove/Trim spaces from cells in excel sheet using vba

Question

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.

Instead of using UsedRange, why not Selection? Or get the range from an input box. — BigBen
– BigBen, Commented Aug 21, 2019 at 18:05
OR Find the last row and the last column and then construct your range — Siddharth Rout
– Siddharth Rout, Commented Aug 21, 2019 at 18:18
If you really want to speed this up, you may want to capture your range as a variant array, make your adjustments to the array, then transpose the array back to your sheet. — Cyril
– Cyril, Commented Aug 21, 2019 at 19:18
If you don't mind using an add-in instead of coding this yourself, I can recommend asap-utilities.com It is an indispensable tool for me. — Rno
– Rno, Commented Aug 21, 2019 at 19:24

Ron Rosenfeld · Accepted Answer · 2019-08-21 19:22:07Z

2

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

answered Aug 21, 2019 at 19:22

Ron Rosenfeld

62.1k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Remove/Trim spaces from cells in excel sheet using vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related