Clear cell contents based on value of another cell range using VBA

Solved/Closed
Tbonekiller Posts 17 Registration date Wednesday August 21, 2019 Status Member Last seen June 6, 2024 - Jan 6, 2021 at 02:35 PM
Tbonekiller Posts 17 Registration date Wednesday August 21, 2019 Status Member Last seen June 6, 2024 - Jan 12, 2021 at 01:06 PM
I'm new to all of this and I'm stumbling through as best as I can so forgive me if I don't explain this very well.

I have a workbook that has over 180 worksheets in it.

I store ongoing data on each worksheet in cells a6:i6 through a2506:i2506.

At the start of the new year I only need to keep "Active" data.

I want to be able to compare cell b6 through b2506 individually and make sure it doesn't match ANY cell in the range k8:k508 collectively and then clear contents of range a:i for each row b6:b2506 that doesn't match.

I can't use a simple compare b to k because column k is a consolidated (removing duplicates) list of b and therefore I need to keep every row in b that matches ANY of the rows in k.

I can't delete rows either, I have to use clear contents because there is information in the other columns that populate off information entered in columns a through i.

If anyone has a quick way to do this using VBA then I can run it on each sheet individually or consolidate to run on each sheet at once by just calling the new worksheet and then the sub to clear contents.

Thank you in advance for any help or thoughts to point me in the right direction.
Related:

4 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jan 12, 2021 at 11:59 AM
Hi Tbonekiller,

Again that's an issue I'm not having, same with the brackets around the sheet names. I guess we have different VBA settings that I'm not aware of.

We can let Excel know to look at the whole cell. To do that, change:
Set mFind = Range("K8:K38").Find(cell.Value)

into:
Set mFind = Range("K8:K38").Find(cell.Value, lookat:=xlWhole)


Does that solve it for you?

Now you know the power of VBA, it's quite the time saver.

Best regards,
Trowa
1
Tbonekiller Posts 17 Registration date Wednesday August 21, 2019 Status Member Last seen June 6, 2024
Jan 12, 2021 at 01:06 PM
Trowa,

PERFECT!!!!!! Thank you so much. This really helped me a lot.
0