CODEWITHSUNDEEP
excelvba
ChartProblems
ChartProblems

Reputation: 437

VBA clear content of rows next to cells

Each cell in a specific column either contains an 'x' or is empty. I want to create a sub which finds all the x's and then delete the content of all cells to the right in that same row. I am not sure how to properly select and clearContents of the rows in the IF statement below. Any help is much appreciated.

    Sub clearContents()
        With Sheets("Main")
            Dim c As Range
            For Each c In Range("B1:B23")
                If c.Value = "x" Then
                    c.Offset(0, 1).End(xlToRight).clearContents
                End If
            Next c
         End With
    End Sub

Upvotes: 1

Views: 332

Answers (1)

SJR
SJR

Reputation: 23081

Try this. By the way you were missing a dot in front of the Range which you need for your With statement.

Sub clearContents()
    Dim c As Range, c1 As Long
    With Sheets("Main")
        For Each c In .Range("B1:B23")
            If c.Value = "x" Then
                c1 = Cells(c.Row, Columns.Count).End(xlToLeft).Column
                If c1 > 2 Then c.Offset(0, 1).Resize(, c1 - 2).clearContents
            End If
        Next c
     End With
End Sub

Upvotes: 1

Related Questions