VBA find value in range and clear cells

adam199311

New Member
Joined
Aug 2, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hi, I am very new to VBA and I am looking for a solution please.

When a value is entered in cells A5, A6, A7 or A8 I would like any cells in the range A14:C25 that match to have the contents cleared. Is there a basic solution that I could implement please?

Thanks
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Right-click on the sheet tab name at the bottom of the screen, select "View Code", and copy and paste this code in the resulting VB Editor window.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    
'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   See if cell is updated in watched range
    If (Not Intersect(Target, Range("A5:A8")) Is Nothing) And (Target.Value <> "") Then
        Application.EnableEvents = False
'       Loop through each cell in other range
        For Each cell In Range("A14:C25")
'           See if it matches and clear value
            If cell.Value = Target.Value Then cell.ClearContents
        Next cell
        Application.EnableEvents = True
    End If

End Sub
This code will run automatically, as you updates values in the range A5:A8.
 
Upvote 0
Solution
Right-click on the sheet tab name at the bottom of the screen, select "View Code", and copy and paste this code in the resulting VB Editor window.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
   
'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
   
'   See if cell is updated in watched range
    If (Not Intersect(Target, Range("A5:A8")) Is Nothing) And (Target.Value <> "") Then
        Application.EnableEvents = False
'       Loop through each cell in other range
        For Each cell In Range("A14:C25")
'           See if it matches and clear value
            If cell.Value = Target.Value Then cell.ClearContents
        Next cell
        Application.EnableEvents = True
    End If

End Sub
This code will run automatically, as you updates values in the range A5:A8.
Thank you very much for your quick response! Worked perfectly!
 
Upvote 0
' Loop through each cell in other range
For Each cell In Range("A14:C25")
' See if it matches and clear value
If cell.Value = Target.Value Then cell.ClearContents
Next cell
Joe, instead of running a loop through each cell, why not use the Range's Replace function to replace the Target.Value with the empty string ("") when it is the whole contents of the cell?
VBA Code:
Range("A14:C25").Replace Target.Value, "", xlWhole, , False, , False, False
 
Upvote 0
Joe, instead of running a loop through each cell, why not use the Range's Replace function to replace the Target.Value with the empty string ("") when it is the whole contents of the cell?
VBA Code:
Range("A14:C25").Replace Target.Value, "", xlWhole, , False, , False, False
Cause I hadn't thought of that.
Its the weekend, my brain is on vacation!
 
Upvote 0
Hi there,

I have the same application of the code mentioned here, but with a little twist.

Cells from F8:AJ8 that contains "Fri" (These cells value updates when a month is selected in another cell "F1" drop down list in the "Summary" sheet), should automatically clear all cells in its column containing the value "10". The reason for being specific with the value is because the sheet has a tendency to increase rows or delete rows and i wouldn't want it to clear any other not matching values beyond the border. Point is the range will be open without limitation as rows can be added or deleted.

Is there any way to also return the values back to 10 if the month selection in Sheet "Summary" gets changed.


Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range

' Exit if multiple cells updated at once
If Target.CountLarge > 1 Then Exit Sub

' See if cell is updated in watched range
If (Not Intersect(Target, Range("F8:AJ8")) Is Nothing) And (Target.Value <> "") Then
Application.EnableEvents = False
' Loop through each cell in other range
For Each cell In Range("F9:AJ300")
' See if it matches and clear value
If cell.Value = Target.Value Then cell.ClearContents
Next cell
Application.EnableEvents = True
End If

End Sub

1640769478038.png
 
Upvote 0
Hi there,

I have the same application of the code mentioned here, but with a little twist.

Cells from F8:AJ8 that contains "Fri" (These cells value updates when a month is selected in another cell "F1" drop down list in the "Summary" sheet), should automatically clear all cells in its column containing the value "10". The reason for being specific with the value is because the sheet has a tendency to increase rows or delete rows and i wouldn't want it to clear any other not matching values beyond the border. Point is the range will be open without limitation as rows can be added or deleted.

Is there any way to also return the values back to 10 if the month selection in Sheet "Summary" gets changed.


Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range

' Exit if multiple cells updated at once
If Target.CountLarge > 1 Then Exit Sub

' See if cell is updated in watched range
If (Not Intersect(Target, Range("F8:AJ8")) Is Nothing) And (Target.Value <> "") Then
Application.EnableEvents = False
' Loop through each cell in other range
For Each cell In Range("F9:AJ300")
' See if it matches and clear value
If cell.Value = Target.Value Then cell.ClearContents
Next cell
Application.EnableEvents = True
End If

End Sub

View attachment 54127
I May as well do it this way for each column through a command button

Sub ClearCell()
If Range("F8") = "Fri" Then
Range("F9:F").ClearContents
else

If Range("G8") = "Fri" Then
Range("G9:G").ClearContents
End If
End If
End Sub
 
Upvote 0
Hi there,

I have the same application of the code mentioned here, but with a little twist.

Cells from F8:AJ8 that contains "Fri" (These cells value updates when a month is selected in another cell "F1" drop down list in the "Summary" sheet), should automatically clear all cells in its column containing the value "10". The reason for being specific with the value is because the sheet has a tendency to increase rows or delete rows and i wouldn't want it to clear any other not matching values beyond the border. Point is the range will be open without limitation as rows can be added or deleted.

Is there any way to also return the values back to 10 if the month selection in Sheet "Summary" gets changed.


Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range

' Exit if multiple cells updated at once
If Target.CountLarge > 1 Then Exit Sub

' See if cell is updated in watched range
If (Not Intersect(Target, Range("F8:AJ8")) Is Nothing) And (Target.Value <> "") Then
Application.EnableEvents = False
' Loop through each cell in other range
For Each cell In Range("F9:AJ300")
' See if it matches and clear value
If cell.Value = Target.Value Then cell.ClearContents
Next cell
Application.EnableEvents = True
End If

End Sub

View attachment 54127
Unless you have a question about the original question, or your question is EXACTLY the same as the original (which it is not), it is always best to post your question to a NEW thread instead of posting on to someone else's. That way it appears as a new unanswered question, and will show up on the "Unanswered threads" listing that many people use to look for unanswered questions.
 
Upvote 0
Unless you have a question about the original question, or your question is EXACTLY the same as the original (which it is not), it is always best to post your question to a NEW thread instead of posting on to someone else's. That way it appears as a new unanswered question, and will show up on the "Unanswered threads" listing that many people use to look for unanswered questions.
Ok Thanks for the tip
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top