Select a range in Excel based on the value of a cell-CodePudding

I am trying to write a macro to remove the contents of cells in rows containing a given value in a given column.

Finding the value using "For Each" is not a problem but I am struggling to select the range I want to clear after that.

What I've tried is

Dim WorkRng As Range
Set WorkRng = Range("AO8:AO108")
Dim V

For Each V in WorkRng
If V.Value = "the value I'm looking for" Then
V.Columns("A:AM").Select
Selection.ClearContents
End If
Next V

What I am trying to do with the V.Columns line is to select the cells in the row where the value is found from column A to column AM. It seems to clear cells to the right of the value found only.

Edited: to correct grammar only

CodePudding user response：

Here is one way:

Dim WorkRng As Range
Set WorkRng = Range("AO8:AO108")
Dim V As Range

For Each V In WorkRng.Cells
    If V.Value = "the value I'm looking for" Then
        V.Offset(0, -40).Resize(1, 39).ClearContents
    End If
Next V

Another option would be:

Intersect(V.EntireRow, Range("A:AM")).ClearContents

To clarify why it is clearing cells to the right of the value found:

When you link multiple ranges together, the second range becomes relative to the first.

When VBA translates your line,

V.Columns("A:AM").Select

it is returning the 40 columns relative to "AO".

In the Immediate window:

Set V = Range("AO100")
? V.Address
$AO$100
? V.Columns("A:AM").address
$AO$100:$CA$100