Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Please help with the vba code

0
Please help with the vba code to delete data in the area (K3:O17) when it overlaps with data in the area (E8:H8), thank you very much
Answer
Discuss

Discussion

HGVIET,

You've goofed up again. You never corrected the title in your first post and now you have used the same title in your second post. The title of this - your second post - needs to be changed to "Code to delete value after finding in specified range". You have also posted your question as an answer. In your first post post I explained to you how to post a question but you haven't followed my guidance.
You have made it clearer as to which values to delete (those in the range "K3:O17"). I had already prepared an answer while waiting for you to fix your first post.
I will gladly share it with you but only after you fix both your first post and this post as I have instructed you to do.
WillieD24 (rep: 657) Oct 20, '24 at 12:43 pm
Add to Discussion

Answers

0

HGVIET,

I see that you haven't fixed your posts (both) as instructed, but I've had a change of heart for the benefit other forum members and visitors so they can get answers too.

This solution relates to the file from your first post.

The first thing I added was a check to see if there was a value to search for"

' check that there is a value to search for
If Range("F1").Value = "" Then
    answer = MsgBox("     Enter a value in ''F1''.", vbOKOnly, "  Value is missing.")
    Exit Sub
End If

I also added a check in case the value is not in the range to be searched:

' check that the value is in the search range
 Set where1 = Range("L8:P17").Find(Range("F1").Value)
 If where1 Is Nothing Then
    answer = MsgBox("     ''F1'' value not found.", vbOKOnly, "  Value is missing.")
    Exit Sub
End If

Deleting the value just required one additional line of code after the "flashing" is done:

If i >= freq Then Range(where2).ClearContents   ' delete value

See file attached.

Cheers   :-)

Discuss

Discussion

@HGVIET,

Now that I have provide answer for you it would be appreciated if you would comply with the forum rules: When you get Answers to your Question you must reply or select the best one using the Select Answer button under the Answer.

WillieD24 (rep: 657) Oct 25, '24 at 9:42 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login