Clear cell contents based on value of another cell range using VBA Solved/Closed

Question

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.

TrowaD · Answer

Hi Tbonekiller, You explain it better then a lot of others :). There is no need to run the code on each sheet manually, the code will loop through all sheets. I added a line where you can add sheets you might want to exclude from the cleansing. Here is the code: Sub RunMe() Dim ws As Worksheet Dim mFind As Range Application.ScreenUpdating = False For Each ws In Worksheets ws.Select If ws.Name <> "Not this sheet" And ws.Name <> "Not this sheet either" Then 'Add the sheet names that need to be excluded here For Each cell In Range("B6:B2506") Set mFind = Range("K8:K508").Find(cell.Value) If mFind Is Nothing Then Range(Cells(cell.Row, "A"), Cells(cell.Row, "I")).ClearContents End If Next cell End If Next ws Application.ScreenUpdating = True End Sub Best regards, Trowa

Tbonekiller · Answer

Trowa,

Thank you for your reply. 

I put the code in it's own module and added the sheets (I had to add the ( ) before and after the " " for it to recognize the sheet names - remember I'm still learning) that I didn't want disturbed because at first it wiped out information on these sheets. I'm using a back-up copy of the file so it's ok.

It seems that on the sheets that I want to look at it's not actually doing anything though. Before I added the ( ) to the sheet names I wanted to skip it was wiping out the contents on those sheets, but still wasn't doing anything on the sheets that I wanted it to look at.

The sheets that I want to skip don't have anything in column K if you think that is making the difference.

Is there anything else that I might be missing that you thought I might know instinctively? I told you I'm pretty new to all this so I don't know much....lol

TrowaD · Answer

Hi Tbonekiller,

Sorry to hear you are having trouble with my solution. 

The only change you might have to make, was to change the sheetnames on code line 9. No need for brackets, otherwise I would have put them there. It is a working code.

Let me post my testbook, so you can see the test environment I used to come up with the code:
http://www.ge.tt/6r1dnGA3

Maybe you see something that I misunderstood and you can let me know.

Otherwise post the download link to your own workbook (always be carefull with sensitive data), so I can have a look as to what you do different then what I did to create the code.

Best regards,
Trowa

TrowaD · Answer

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

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

4 responses

Similar discussions

Subscribe To Our Newsletter!