Find match in list if match skip no match delete row

J

justvba

New Member
Joined
Jan 6, 2017
Messages
41
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
 
Last edited:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
hiker95 said:
justvba,

We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data worksheets, and, what the results worksheet should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729
Click to expand...

the problem with this is it will not be meaningful that is why I did not include it.... I have a lists of parts

sheet 1
tire
mirror
hood
door
shoe
shirt
toys


sheet2

tire
hood
apples
grapes
orange
red
shorts
toys
toys
tire
shoe
computer
computer
soda
 
Upvote 0
justvba,

If you are not able to provide screenshots, then:

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com
 
Last edited:
Upvote 0
hiker95 said:
justvba,

If you are not able to provide screenshots, then:

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com
Click to expand...

Im a little confused that is what is in my workbook. it is 2 sheets with that information on each sheet and that VBA I need to post a picture of that???? makes no sense..
 
Upvote 0
justvba,

If you can not display at least screenshots of both worksheets, of before, and, after, then I can not help you.
 
Upvote 0
justvba,

Here are some screenshots of Sheet1, and, Sheet2, based on your flat text display in your reply #3.


Excel 2007
A
1tire
2mirror
3hood
4door
5shoe
6shirt
7toys
8
Sheet1



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


I am confused by your flat text displays, and, your macro.

1. Are their titles in row 1, in both sheets?

2. Are the above screenshots correct in reference to their sheetnames, and, cell locations?

3. Is the text in both sheets/columns in the correct rows?
 
Upvote 0
hiker95 said:
justvba,

Here are some screenshots of Sheet1, and, Sheet2, based on your flat text display in your reply #3.

Excel 2007
A
1
tire
2
mirror
3hood
4door
5shoe
6shirt
7toys
8

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



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

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



I am confused by your flat text displays, and, your macro.

1. Are their titles in row 1, in both sheets?

2. Are the above screenshots correct in reference to their sheetnames, and, cell locations?

3. Is the text in both sheets/columns in the correct rows?
Click to expand...

I adjusted the sheet name of sheet2 and added a column title but dont need it as long as data starts in a2
sheet 1 the data starts at "A14" but yes everything is correct.
 
Upvote 0
justvba,

Here is a macro solution for you to consider.

Sample raw data worksheets:


Excel 2007
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14tire
15mirror
16hood
17door
18shoe
19shirt
20toys
21
Sheet1



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


And, after the macro in worksheet Current Price:


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


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code: 
Sub justvba()
' hiker95, 04/07/2017, ME999665
Dim w1 As Worksheet, wp As Worksheet
Dim r As Long, lr As Long, n As Range
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")
Set wp = Sheets("Current Price")
With wp
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  For r = lr To 2 Step -1
    Set n = w1.Columns(1).Find(.Cells(r, 1).Value, LookAt:=xlWhole)
    If n Is Nothing Then
      .Rows(r).Delete
    End If
  Next r
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the justvba macro.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
VBA - Deleting Entire Row Based on a Condition
Replies
2
Views
464
jolivanes
jolivanes
B
  • Question
Finding excluded values in excel
Replies
0
Views
206
bcmk29
B
rjplante
  • Solved
Change from Set as Default to Set as Active printer
Replies
1
Views
193
NdNoviceHlp
NdNoviceHlp
M
  • Question
Multiple users using same user form having errors and slow at times
Replies
2
Views
209
Joe4
Joe4
P
  • Question
Error on Loop
Replies
3
Views
274
Pestomania
P

Forum statistics

Threads
1,214,522
Messages
6,120,022
Members
448,939
Latest member
Leon Leenders

Share this page

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