Excel: Clear contents based on contents in another sheet


Hello everyone. New VBA user here and in need of some help. I have a workbook with two sheets, “Max Data” and “Min Data”. Both sheets have the same number of columns with data in columns A and B being the same. Columns C and on have unique values.

Part Number Serial Number Data Data
1 ABCD-1 ABC ABC
2 ABCD-2 ABC ABC
3 ABCD-3 ABC ABC
4 ABCD-4 ABC ABC
5 ABCD-5 ABC ABC
Part Number Serial Number Data Data
1 ABCD-1 ABC ABC
2 ABCD-2 ABC ABC
3 ABCD-3 ABC ABC
4 ABCD-4 ABC ABC
5 ABCD-5 ABC ABC
I have a macro that will clear contents on the “Max Data” sheet based on certain criteria. The result will look something like this:
Part Number Serial Number Data Data
1 ABCD-1 ABC ABC
2 ABCD-3 ABC ABC
3 ABCD-5 ABC ABC
4
5
Part Number Serial Number Data Data
1 ABCD-1 ABC ABC
2 ABCD-2 ABC ABC
3 ABCD-3 ABC ABC
4 ABCD-4 ABC ABC
5 ABCD-5 ABC ABC
What I need is a macro that compares the values in Column B of both sheets and clears contents from the “Min Data” sheet if it’s not found on the “Min Data” sheet. Looking at the images above the value in B7 on the “Min Data” sheet is not found in column B on the “Max Data” sheet. I need to clear the contents from the “Min Data” sheet also. The calculations in the last row of both sheets is used elsewhere hence the need to clear contents rather than delete the entire row. Any help would be greatly appreciated! Edit: Reading this after posting it looks really confusing. Sorry about that.


This question generated 10 answers. To proceed to the answers, click here.

This thread is current as of June 24, 2015.


For more resources for Microsoft Excel