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

0 votes

I'm attempting to create a macro that will empty cells in rows that contain a specific value in a specific column. Finding the value using "For Each" is not difficult, however choosing the range to clear afterward is difficult for me.

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.

Jan 29, 2023 in Others by Kithuzzz
• 38,010 points
440 views

1 answer to this question.

0 votes

Try this:

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
answered Jan 29, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

VBA Loop to select then copy a range of cells based on value in column B

Try this: Sub Macro2() Dim ...READ MORE

answered Mar 23, 2023 in Others by narikkadan
• 63,420 points
1,638 views
0 votes
1 answer

MS Excel showing the formula in a cell instead of the resulting value

Make sure that... There's an = sign before the formula There's ...READ MORE

answered Nov 8, 2022 in Others by narikkadan
• 63,420 points
531 views
0 votes
1 answer

Excel Define a range based on a cell value

Let's say that cells A1, A2, A3, ...READ MORE

answered Nov 8, 2022 in Others by narikkadan
• 63,420 points
2,564 views
0 votes
1 answer

Attributing the value of a cell based on another corresponding cell

Put in cell B1 of Sheet1: =INDEX(Sheet2!B:B;MATCH(A1;Sheet2!A:A;0)) Drag it ...READ MORE

answered Jan 23, 2023 in Others by narikkadan
• 63,420 points
245 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
877 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,183 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
481 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
731 views
0 votes
1 answer

Get random value in the range of plus/minus 10% of a cell value in Excel

Why not just use RANDBETWEEN(B2*0.9, B2*1.1) if ...READ MORE

answered Dec 25, 2022 in Others by narikkadan
• 63,420 points
441 views
0 votes
1 answer

Trying to run different macros based on value of cell in a range

This demonstrates one approach to loop through ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,420 points
268 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP