CODEWITHSUNDEEP
excelvba
Xavi
Xavi

Reputation: 207

How to clear content of rows except column A?

I have a macro which removes the content of the entire row with cells in light yellow in my selection (in my macro Range("B1:B3000")).

enter image description here

My macro works but I would like to not remove the content of column A.

I guess I should modify the line: rColored.EntireRow.ClearContents

Sub SelectColoredCellsVariantremovecelllightyellow()
    Dim rCell As Range
    Dim lColor As Long
    Dim rColored As Range
    Dim myselection As Range
    Set myselection = Range("B1:B3000")
    lColor = 10092543
    Set rColored = Nothing
    For Each rCell In myselection
        If rCell.Interior.Color = lColor Then
            If rColored Is Nothing Then
                Set rColored = rCell
            Else
                Set rColored = Union(rColored, rCell)
            End If
        End If
    Next
    rColored.EntireRow.ClearContents
    Set rCell = Nothing
    Set rColored = Nothing
End Sub

Upvotes: 1

Views: 196

Answers (1)

BigBen
BigBen

Reputation: 50162

Here you can use Intersect to restrict the range to be cleared:

Replace

rColored.EntireRow.ClearContents

with

If Not rColored Is Nothing Then
    Intersect(rColored.EntireRow, Columns("B:AB")).ClearContents
End If

Upvotes: 1

Related Questions