Removing leading and trailing spaces in a range but limited to the last active cell

[David Montoya]

Gentlemen,


I need to apply the following code to a selected range; however, also need to limit the loop function to the last active cell of the range (otherwise it takes long time to go though the range), or to apply the code only to active cells of the range, instead of hard coding first column rows 4 to 1004.

Your assistance is appreciated:

Sub RemoveLeadingTrailing()
For i = 4 To 1004
    Do While InStr(ActiveSheet.Cells(i, 1), "  ") > 0
        ActiveSheet.Cells(i, 1).Value = Replace(ActiveSheet.Cells(i, 1), "  ", " ")
    Loop
        ActiveSheet.Cells(i, 1).Value = Trim(ActiveSheet.Cells(i, 1).Value)
Next i
End Sub

 

[Rick Rothstein]

Gentlemen,


I need to apply the following code to a selected range; however, also need to limit the loop function to the last active cell of the range (otherwise it takes long time to go though the range), or to apply the code only to active cells of the range, instead of hard coding first column rows 4 to 1004.

Your assistance is appreciated:

Sub RemoveLeadingTrailing()
For i = 4 To 1004
    Do While InStr(ActiveSheet.Cells(i, 1), "  ") > 0
        ActiveSheet.Cells(i, 1).Value = Replace(ActiveSheet.Cells(i, 1), "  ", " ")
    Loop
        ActiveSheet.Cells(i, 1).Value = Trim(ActiveSheet.Cells(i, 1).Value)
Next i
End Sub

There may be a faster way to do this, but I have a question first. Your thread title say leading/trailing spaces... could there be any multiple (adjacent) spaces within the text itself that would need to be preserved as is (that is, the multiple adjacent spaces would need to remain as multiple adjacent spaces)?

 

[David Montoya]

Rick,

Any number of spaces (adjacent or not) between the text need to be preserved. The suppression needs to be to a leading and/or trailing spaces only.

The workbook I have been working on it in column "A" we input the desired value as text; several macros at other columns activate different results depending on existing data or not. If the input does not have any result from columns "B" on, then we need to enter the information as a new record.

The problem I am facing is when entering values at column “A”, most likely using copy and paste, sometimes contains leading and/or trailing spaces, and it creates bad data. So, I am thinking to have the code triggered by a cell value change within the range of A4:A1004. This, to work as a cleanup process, before appending to a master data repository.

Any other code different than I have done above is welcome.

Thanks a lot!

 

[Rick Rothstein]

Rick,

Any number of spaces (adjacent or not) between the text need to be preserved. The suppression needs to be to a leading and/or trailing spaces only.

Alright, I just looked at your posted code in detail... you appear to be replacing instances of 2 adjacent spaces with single spaces... that would mean to me that you are not preserving multiple adjacent spaces whereas your last message says you are. Please clarify.

 

[David Montoya]

I have found through my data verification, that sometimes there was a single to multiple spaces at the beginning, and also at the end. When I run a test with this code and having any number of leading or trailing spaces, the code removes them all, leaving the spaces within the text.
Since my purpose is to have the code trigger by cell change, maybe the code can be redefined to instead of pre-defining to affect the complete range, just to be activated if a cell within the range changes.

 

[Rick Rothstein]

Since my purpose is to have the code trigger by cell change, maybe the code can be redefined to instead of pre-defining to affect the complete range, just to be activated if a cell within the range changes.

Give this Change Event code a try...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  If Not Intersect(Target, Range("A4:A1004")) Is Nothing Then
    Application.EnableEvents = False
    For Each Cell In Intersect(Target, Range("A4:A1004"))
      If Len(Cell.Value) Then Cell.Value = Trim(Cell.Value)
    Next
    Application.EnableEvents = True
  End If
End Sub

 

[David Montoya]

Rick,
It absolutely did the desired work. Thanks a lot.