CODEWITHSUNDEEP
excelvba
Vincent Cheng
Vincent Cheng

Reputation: 3

How to delete blank characters before word in the cell for the column -VBA

So currently in column B, there are cells that have no spaces before them but there are also cells that do.

For example: cell B1 would just be "Market:" but then cell B4, B5,B6,B10, B14, etc would have "_____Total:" ( __ meaning blank space)

What would I have to write to delete the 5 spaces before "Total:"?

I currently have:

    mos = Range("B:B")

    For Each x In mos
        xv = x.Value
        If Left(xv, 1) = "" Then xv = Right(xv, Len(xv) - 1)

    Next xv

which gives me an error.

Please let me know what I should do.

Thanks!

Upvotes: 0

Views: 1295

Answers (3)

QHarr
QHarr

Reputation: 84465

  1. Use Trim$ function. Trim function removes excess trailing and leading white space. Trim$ is a typed function and is faster.
  2. Using Intersect, as below, means only processing the necessary number of rows and not the entire column B.
  3. Be explicit. Put Option Explicit at the top of your code to force variable declaration and declare your variables. And fully qualify which sheet you are working with.
  4. As noted in another answer, there are variants such as LTrim$ just to remove from left (Start of string) and RTrim$, just to remove from right (end of string).

Code:

Option Explicit
Public Sub test()

    Dim mos As Range
    Dim x As Range

    With ActiveSheet                             'change to appropriate worksheetname

        Set mos = .Range("B:B")

        For Each x In Intersect(mos, .UsedRange)
            x = Trim$(x)
        Next x

    End With

End Sub

Bonus:

There are a lot of existing functions written to "clean" strings removing all "extra" white space characters i.e. leaving only single internal white spaces.

Here is one by Henry Habermacher

Public Function removeObsoleteWhiteSpace _
  (FromString As Variant) As Variant
  If IsNull(FromString) Then 'handle Null values
    removeObsoleteWhiteSpace = Null
    Exit Function
  End If
  Dim strTemp As String
  strTemp = Replace(FromString, vbCr, " ")
  strTemp = Replace(strTemp, vbLf, " ")
  strTemp = Replace(strTemp, vbTab, " ")
  strTemp = Replace(strTemp, vbVerticalTab, " ")
  strTemp = Replace(strTemp, vbBack, " ")
  strTemp = Replace(strTemp, vbNullChar, " ")
  While InStr(strTemp, "  ") > 0
    strTemp = Replace(strTemp, "  ", " ")
  Wend
  strTemp = Trim(strTemp)
  removeObsoleteWhiteSpace = strTemp
End Function

Example call:

Public Sub testing()

    Debug.Print removeObsoleteWhiteSpace("  my string    with a few      spaces    ")

End Sub

Upvotes: 2

DisplayName
DisplayName

Reputation: 13386

To avoid looping you can use:

Dim myAddress As String
myAddress = "B1:B" & Cells(Rows.Count, 2).End(xlUp).Row
Range(myAddress) = Evaluate("IF(" & myAddress & "="""","""",TRIM(" & myAddress & "))")

which can be also written as:

With Range("B1", Cells(Rows.Count, 2).End(xlUp))
    .Value = Evaluate("IF(" & .Address & "="""","""",TRIM(" & .Address & "))")
End With

or Replace() method:

Range("B1", Cells(Rows.Count, 2).End(xlUp)).Replace what:=" ", replacement:="", lookat:=xlPart

of course all above solutions assume that you only have extra spaces at the beginning of each cell content, or they will trim (first two solutions) or eliminate (3rd solution) other spaces also

if you actually have the need to eliminate the leading spaces (the ones preceding the first not blank character) leaving all other ones in place, then you have to loop and use LTrim() VBA function:

Dim cell As Range
For Each cell In Range("B1", Cells(Rows.Count, 2).End(xlUp))
    cell.Value = LTrim(cell.Value)
Next

Upvotes: 2

Mike Mann
Mike Mann

Reputation: 546

Sorry, didn't realize your loop was only to remove spaces.

This is how I trim my spaces:

Sub trim()
Dim mos As Range

    Set mos = Range("B:B")
    mos.Value = Application.trim(mos)

End Sub

Upvotes: 1

Related Questions