SOLVED

Macro to clear cells adjacent to cells with text

%3CLINGO-SUB%20id%3D%22lingo-sub-1394639%22%20slang%3D%22en-US%22%3EMacro%20to%20clear%20cells%20adjacent%20to%20cells%20with%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1394639%22%20slang%3D%22en-US%22%3E%3CP%3EFor%20each%20row%2C%20I%20need%20to%20clear%20the%20cell%20in%20column%20B%20if%20column%20A%20contains%20any%20text.%20The%20number%20of%20rows%20may%20vary.%20The%20attached%20example%20has%20Before%20and%20After%20sheets.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1394639%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1394939%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20to%20clear%20cells%20adjacent%20to%20cells%20with%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1394939%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F668666%22%20target%3D%22_blank%22%3E%40LonnieCurrier%3C%2FA%3E%26nbsp%3BTry%20this%20very%20small%20macro.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20ClearIfText()%0A%0ADim%20DataRange%20As%20Object%0ADim%20i%20As%20Integer%0A%0ASet%20DataRange%20%3D%20Range(%22A1%22).CurrentRegion%0A%0AFor%20i%20%3D%201%20To%20DataRange.Rows.Count%0A%0A%20%20%20%20If%20DataRange.Cells(i%2C%201)%20%26lt%3B%26gt%3B%20%22%22%20Then%0A%20%20%20%20%20%20%20%20DataRange.Cells(i%2C%202).ClearContents%0A%20%20%20%20End%20If%0ANext%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

For each row, I need to clear the cell in column B if column A contains any text. The number of rows may vary. The attached example has Before and After sheets.

1 Reply
Highlighted
Best Response confirmed by LonnieCurrier (Occasional Contributor)
Solution

@LonnieCurrier Try this very small macro.

Sub ClearIfText()

Dim DataRange As Object
Dim i As Integer

Set DataRange = Range("A1").CurrentRegion

For i = 1 To DataRange.Rows.Count

    If DataRange.Cells(i, 1) <> "" Then
        DataRange.Cells(i, 2).ClearContents
    End If
Next

End Sub