Clear contents based on contents in another sheet

n3wpv

New Member
Joined
Jun 24, 2015
Messages
18
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 NumberSerial NumberDataData
1XXXX-1XXXXXX
2XXXX-2XXXXXX
3XXXX-3XXXXXX
4XXXX-4XXXXXX
5XXXX-5XXXXXX

<tbody>
</tbody>
Part NumberSerial NumberDataData
1XXXX-1XXXXXX
2XXXX-2XXXXXX
3XXXX-3XXXXXX
4XXXX-4XXXXXX
5XXXX-5XXXXXX

<tbody>
</tbody>

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 NumberSerial NumberDataData
1XXXX-1XXXXXX
2XXXX-3XXXXXX
3XXXX-5XXXXXX
4
5

<tbody>
</tbody>
Part NumberSerial NumberDataData
1XXXX-1XXXXXX
2XXXX-2XXXXXX
3XXXX-3XXXXXX
4XXXX-4XXXXXX
5XXXX-5XXXXXX

<tbody>
</tbody>

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.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

Your EDIT on the original post is accurate. Maybe if you provide sufficient detail, label sheet names and show a before and after comparison to indicate what you want to achieve, someone can help you.
 

n3wpv

New Member
Joined
Jun 24, 2015
Messages
18
Your EDIT on the original post is accurate. Maybe if you provide sufficient detail, label sheet names and show a before and after comparison to indicate what you want to achieve, someone can help you.

I tried to add screenshots but was unable to.
 

n3wpv

New Member
Joined
Jun 24, 2015
Messages
18
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.

Excel 2012
ABCD
1PartSerial NumberPR
2
31D13-00-00-10.01680.0233
42D13-00-00-40.02560.0134
53D13-00-00-50.02960.0124
64D13-00-00-60.02810.0131
75D13-00-00-70.02250.0118
86D13-00-00-90.02940.0129
97D13-00-00-120.03080.0135
108D13-00-00-130.03020.0142
119D13-00-00-150.02880.0124
1210D13-00-00-160.02910.0143
1311D13-00-00-170.01990.0173
1412D13-00-00-210.02680.0153
1513D13-00-00-220.01890.0123
1614
1715
1816
1917
2018

<tbody>
</tbody>
Max Data

Excel 2012
ABCD
1PartSerial NumberPR
2
31D13-00-00-10-0.0031
42D13-00-00-40.0011-0.0025
53D13-00-00-5-0.0005-0.0033
64D13-00-00-6-0.0011-0.0033
75D13-00-00-70.0009-0.0028
86D13-00-00-9-0.0004-0.0028
97D13-00-00-12-0.0006-0.0029
108D13-00-00-130.0002-0.0028
119D13-00-00-15-0.0006-0.0017
1210D13-00-00-160.0005-0.003
1311D13-00-00-17-0.0008-0.0028
1412D13-00-00-21-0.0012-0.0032
1513D13-00-00-220.0001-0.0025
1614
1715
1816
1917
2018

<tbody>
</tbody>
Min Data

I have a macro that will clear contents on the “Max Data” sheet based on certain criteria. The result will look something like this:

Excel 2012
ABCD
1PartSerial NumberPR
2
31D13-00-00-10.01680.0233
42D13-00-00-40.02560.0134
53D13-00-00-50.02960.0124
64D13-00-00-60.02810.0131
75D13-00-00-70.02250.0118
86D13-00-00-90.02940.0129
97D13-00-00-150.02880.0124
108D13-00-00-160.02910.0143
119D13-00-00-170.01990.0173
1210D13-00-00-210.02680.0153
1311D13-00-00-220.01890.0123
1412
1513
1614
1715
1816
1917
2018

<tbody>
</tbody>
Max Data

Excel 2012
ABCD
1PartSerial NumberPR
2
31D13-00-00-10-0.0031
42D13-00-00-40.0011-0.0025
53D13-00-00-5-0.0005-0.0033
64D13-00-00-6-0.0011-0.0033
75D13-00-00-70.0009-0.0028
86D13-00-00-9-0.0004-0.0028
97D13-00-00-12-0.0006-0.0029
108D13-00-00-130.0002-0.0028
119D13-00-00-15-0.0006-0.0017
1210D13-00-00-160.0005-0.003
1311D13-00-00-17-0.0008-0.0028
1412D13-00-00-21-0.0012-0.0032
1513D13-00-00-220.0001-0.0025
1614
1715
1816
1917
2018

<tbody>
</tbody>
Min Data


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!
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

See if this does what you want on a copy of your workbook. Note the comments on sheet names which I assumed to be "Max Data" and "Min Data".
Code:
Sub n3wpv()
Dim S1 As Worksheet, S2 As Worksheet
Dim R1 As Range, R2 As Range, c As Range
Set S1 = Sheets("Max Data") '<--change sheet name to suit
Set S2 = Sheets("Min Data") '<--change sheet name to suit
Set R1 = S1.Range("B3:B" & S1.Cells(Rows.Count, "B").End(xlUp).Row)
Set R2 = S2.Range("B3:B" & S2.Cells(Rows.Count, "B").End(xlUp).Row)
Application.ScreenUpdating = False
For Each c In R2
   If IsError(Application.Match(c.Value, R1, 0)) Then R2.Rows(c.Row - 2).Resize(1, 3).ClearContents
Next c
Application.ScreenUpdating = True
End Sub
 

n3wpv

New Member
Joined
Jun 24, 2015
Messages
18
See if this does what you want on a copy of your workbook. Note the comments on sheet names which I assumed to be "Max Data" and "Min Data".
Code:
Sub n3wpv()
Dim S1 As Worksheet, S2 As Worksheet
Dim R1 As Range, R2 As Range, c As Range
Set S1 = Sheets("Max Data") '<--change sheet name to suit
Set S2 = Sheets("Min Data") '<--change sheet name to suit
Set R1 = S1.Range("B3:B" & S1.Cells(Rows.Count, "B").End(xlUp).Row)
Set R2 = S2.Range("B3:B" & S2.Cells(Rows.Count, "B").End(xlUp).Row)
Application.ScreenUpdating = False
For Each c In R2
   If IsError(Application.Match(c.Value, R1, 0)) Then R2.Rows(c.Row - 2).Resize(1, 3).ClearContents
Next c
Application.ScreenUpdating = True
End Sub

This is close. I need to only look in B3:B44 for the comparison. It's trying to clear contents below B44 which gives me an error because there are merged cells below. The cells to be cleared would be in B3:AC.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
This is close. I need to only look in B3:B44 for the comparison. It's trying to clear contents below B44 which gives me an error because there are merged cells below. The cells to be cleared would be in B3:AC.

That's the kind of information you should supply at the outset - saves us both time. Try this:
Code:
Sub n3wpv()
Dim S1 As Worksheet, S2 As Worksheet
Dim R1 As Range, R2 As Range, c As Range
Set S1 = Sheets("Max Data") '<--change sheet name to suit
Set S2 = Sheets("Min Data") '<--change sheet name to suit
Set R1 = S1.Range("B3:B" & S1.Cells(Rows.Count, "B").End(xlUp).Row)
Set R2 = S2.Range("B3:B44")
Application.ScreenUpdating = False
For Each c In R2
   If IsError(Application.Match(c.Value, R1, 0)) Then R2.Rows(c.Row - 2).Resize(1, 28).ClearContents
Next c
Application.ScreenUpdating = True
End Sub
 

n3wpv

New Member
Joined
Jun 24, 2015
Messages
18
That's the kind of information you should supply at the outset - saves us both time. Try this:
Code:
Sub n3wpv()
Dim S1 As Worksheet, S2 As Worksheet
Dim R1 As Range, R2 As Range, c As Range
Set S1 = Sheets("Max Data") '<--change sheet name to suit
Set S2 = Sheets("Min Data") '<--change sheet name to suit
Set R1 = S1.Range("B3:B" & S1.Cells(Rows.Count, "B").End(xlUp).Row)
Set R2 = S2.Range("B3:B44")
Application.ScreenUpdating = False
For Each c In R2
   If IsError(Application.Match(c.Value, R1, 0)) Then R2.Rows(c.Row - 2).Resize(1, 28).ClearContents
Next c
Application.ScreenUpdating = True
End Sub

Works perfectly, thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,961
Messages
5,599,061
Members
414,281
Latest member
Engjamal2021

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
Top