Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Clear cell contents based on value of another cell range using VBA [Solved]

Report
Posts
10
Registration date
Wednesday August 21, 2019
Status
Member
Last seen
January 22, 2021
 -
Posts
10
Registration date
Wednesday August 21, 2019
Status
Member
Last seen
January 22, 2021
 -
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.
See more

4 replies

Answer 1 / 4
Posts
2696
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 28, 2021
 457
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
Comment
1
Thank you
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2942 users have said thank you to us this month

Report
Posts
10
Registration date
Wednesday August 21, 2019
Status
Member
Last seen
January 22, 2021
Trowa,

PERFECT!!!!!! Thank you so much. This really helped me a lot.
Answer 2 / 4
Posts
2696
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 28, 2021
 457
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
Answer 3 / 4
Posts
10
Registration date
Wednesday August 21, 2019
Status
Member
Last seen
January 22, 2021
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
Answer 4 / 4
Posts
2696
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 28, 2021
 457
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
Report
Posts
10
Registration date
Wednesday August 21, 2019
Status
Member
Last seen
January 22, 2021
Trowa,

I got ahead of myself, sorry I did mention I'm learning.

The "k" column reference needed to be modified for what I wanted to happen, but works the way you said it would.
Thank You!

I did run into an issue though when comparing to column k it sees 50 as part of 150 and 33 or 34 as part of 1334 because it is looking for any match at all I'm guessing. Is there a way to only match exactly cell by cell still?

I posted the code I'm currently using so you can see I had to modify it a little. I also added a msgbox to tell the user it is done since it does take awhile to perform this.

I really do appreciate all your help, it has turned what took me an hour or so into less than a minute or two. So thank you for your help so far.



The code I'm using:

Sub clear_sheets_new_year_active_only_dies()

Dim ws As Worksheet
Dim mFind As Range

Application.ScreenUpdating = False

For Each ws In Worksheets
ws.Select
If ws.Name <> ("Last Time Die Ran") And ws.Name <> ("Active Dies") And ws.Name <> ("Check Out Sheet") And ws.Name <> ("Summary Report") And ws.Name <> ("Graphs") Then 'Add the sheet names that need to be excluded here
For Each cell In Range("B6:B2506")
Set mFind = Range("K8:K38").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
Update_complete_msgbox
End Sub

Sub Update_complete_msgbox()
VBA.MsgBox "Update Completed!", , "Update Sheets New Year"
End Sub
Reply Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!