Find match in list if match skip no match delete row

[justvba]

Hi I have a large list of parts that has duplicates and that's OK but I need to look at the part numbers on sheet2 and see if it is in sheet1. if in sheet one leave it alone if not in sheet 1 delete row. is there a fast way of doing this I am using 2016 excel VBA. here is my current code



Do Until IsEmpty(ActiveCell)
SearchItem = UCase(Trim(ActiveCell.Text))
OriginalSheet.Select
Range("A14").Select
Do Until ActiveCell.Value = SearchItem Or IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop

If ActiveCell.Value = SearchItem Then
If IsEmpty(ActiveCell.Offset(0, 4)) Then
Sheets("Current Price").Select
ActiveCell.Offset(1, 0).Select
Else
Sheets("Current Price").Select
ActiveCell.EntireRow.Delete
End If

Else
Sheets("Current Price").Select
ActiveCell.EntireRow.Delete
End If

Loop

 

[justvba]

thank you that works great!!
I do want to ask if in the future I have to check the cell next to column a on sheet 1 to see if it has a value in it and if it does also delete the row on "Current Price" sheet how could I do that?
I thought I could add something in the IF n Is Nothing and offset(0,1) <> blank

 

[hiker95]

thank you that works great!!

justvba,

Thanks for the feedback.

You are very welcome. Glad I could help.

 

[hiker95]

I do want to ask if in the future I have to check the cell next to column a on sheet 1 to see if it has a value in it and if it does also delete the row on "Current Price" sheet how could I do that?
I thought I could add something in the IF n Is Nothing and offset(0,1) <> blank

justvba,

I would have to see screenshots of both worksheets, before, and, worksheet Current Price after (manually formatted by you for the new results that you are now looking for).

 

[justvba]

justvba,



Excel 2007

A	B
tire
hood	k
door
shoe5
shirt	34 green
toys
8

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>

mirror

<tbody>
</tbody>

</tbody>

Sheet1
​

Excel 2007

	A
1
2	tire
3	hood
4	apples
5	grapes
6	orange
7	red
8	shorts
9	toys
10	toys
11	tire
12	shoe
13	computer
14	computer
15	soda
16

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>

Current price
​

ok lets try this.....
so I updated the sheets above and the final result would be the same as before but only those part that are blank in column "B" would show on current Price sheet. Make sense?

 

[hiker95]

so I updated the sheets above and the final result would be the same as before but only those part that are blank in column "B" would show on current Price sheet.

justvba,

And, the results on worksheet Current Price would look like?

 

[justvba]

justvba,

And, the results on worksheet Current Price would look like?

only those part that are blank in column "B" would show on current Price sheet.

A
tire
door
shoe5
toys

<tbody>
</tbody>

 

[hiker95]

justvba,

I have some questions before I do any more coding, because your screenshots are not clear to me.

Excel 2007
	A	B	C	D	E	F	G	H	I	J
1	Sheet1	A	B		Current Price	A		Current Price	A
2	1	tire			1			1	tire
3	2				2	tire		2	door
4	3	hood	k		3	hood		3	shoe5
5	4	door			4	apples		4	toys
6	5	shoe5			5	grapes		5
7	6	shirt	34 green		6	orange		6
8	7	toys			7	red		7
9	8	8			8	shorts		8
10					9	toys		9
11					10	toys		10
12					11	tire		11
13					12	shoe		12
14					13	computer		13
15					14	computer		14
16					15	soda		15
17							start			results
Instructions_NEW

In the above screenshots of worksheet Current Price, the results for worksheet Current Price is displayed in my column I?

Do both worksheets have column titles in row 1?

 

[justvba]

justvba,

I have some questions before I do any more coding, because your screenshots are not clear to me.


Excel 2007

	A	B	C	D	E	F	G	H	I	J
1	Sheet1	A	B		Current Price	A		Current Price	A
2	1	tire			1			1	tire
3	2				2	tire		2	door
4	3	hood	k		3	hood		3	shoe5
5	4	door			4	apples		4	toys
6	5	shoe5			5	grapes		5
7	6	shirt	34 green		6	orange		6
8	7	toys			7	red		7
9	8	8			8	shorts		8
10					9	toys		9
11					10	toys		10
12					11	tire		11
13					12	shoe		12
14					13	computer		13
15					14	computer		14
16					15	soda		15
17							start			results

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Instructions_NEW​

In the above screenshots of worksheet Current Price, the results for worksheet Current Price is displayed in my column I?

Do both worksheets have column titles in row 1?

I am confused on why this is becoming so hard. it is the same exact set up as the first question.....
Sheet 1 starts at "A14" Column A = Part Column B = Comment
Sheet 2 (Current Price) data starts at "A2"
Sheet 2 results (Current Price) Data stays at "A2" but only show the Parts where Column B cell is blank

 

[hiker95]

justvba,

Last try!

1. Is the following screenshot correct?

Excel 2007
	A	B
1
2
3
4
5
6
7
8
9
10
11
12
13
14	tire
15
16	hood	k
17	door
18	shoe5
19	shirt	34 green
20	toys
21	8
22
Sheet1

2. Is the following screenshot correct?

Excel 2007
	A
1
2	tire
3	hood
4	apples
5	grapes
6	orange
7	red
8	shorts
9	toys
10	toys
11	tire
12	shoe
13	computer
14	computer
15	soda
16
Current Price

3. And, is the following screenshot correct for the results you are looking for?

Excel 2007
	A
1	tire
2	door
3	shoe5
4	toys
5
6
7
8
9
10
11
12
13
14
15
16
Current Price

 

[justvba]

yes!!