Search code examples
vbaexceltrim

VBA: Trim Cells If Value in Range is

I have the following challenge:

In a worksheet I need to remove unwanted blanks in a certain range of cells via the trim functionality. This already works fine with the following code:

Sub remove_blanks()

Dim rC As Range

Application.ScreenUpdating = False

For Each rC In ActiveSheet.Range("C1:C5000")
    If Not (rC.HasFormula) Then rC = Trim(rC)
Next

Application.ScreenUpdating = True

End Sub

Now I would need to implement a check where the above function should only work if a cell is marked as "YES" in a different column.

E.g. If in column A a cell value is "YES", only then the above function should work for the same cell in column C.

Kind regards, Stefan

Solution

  • Just modify your line of:

    If Not (rC.HasFormula) Then rC = Trim(rC)

    to:

    If Not (rC.HasFormula) And Range("A" & rC.Row).Value = "YES" Then rC = Trim(rC)

    This checks that also the value in column "A" in the same row as rC equals to "YES"