Deleting specific rows.

thesmallfry123

New Member
Joined
Jul 16, 2020
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
Hello there,

We are working on a research project pertaining to the Covid-19 virus. We need to curate a long list of anti-viral agents down to a manageable list. We have removed a few antiviral agents and would like to remove some more. The list (Worksheet 1) labelled below as “List I want to retain” are the rows I would like to not delete from the bigger list (Worksheet 2) containing all the anti-viral agents but I need to delete the remaining rows from the bigger list. There are around 30,000 rows so deleting the rows manually is not feasible. Is there any add-in or built-in Microsoft Excel feature that will let me delete the rows automatically.

Thank you for your time and consideration.
 

Attachments

  • 11714-0003.jpg
    11714-0003.jpg
    155.5 KB · Views: 8
  • 1212.jpg
    1212.jpg
    243.2 KB · Views: 8

thesmallfry123

New Member
Joined
Jul 16, 2020
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
  • download my file from post #6
  • open this file
  • click Show Queries / Connections on Data tab
  • on the right side you should see pane where you can click Queries
  • then double click on the Table there and it will open new window Power Query Editor
the query you provided did the task I was looking for correctly. One question I have is how I can change source 2 and replace a different worksheet with greater number of rows as source 2. Below is the worksheet I would like to use as source to perform the same task as we did with the previous worksheet.
Google Drive Download Link for the Worksheet.

Thank you for the help.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
the query you provided did the task I was looking for correctly. One question I have is how I can change source 2 and replace a different worksheet with greater number of rows as source 2. Below is the worksheet I would like to use as source to perform the same task as we did with the previous worksheet.
Google Drive Download Link for the Worksheet.

Thank you for the help.
Just copy'n'paste data then refresh green table
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,365
Office Version
  1. 365
Platform
  1. Windows
For instance after running the macro the worksheet contains rows such as 50,55, 65,95 and so on that weren't in the worksheet1.
Have a look in Worksheet1 at cells
A23297 (50)
A20124 (55)
A22642 (95)

For row65 I'm not sure how it ended up in worksheet2 as it wasn't in worksheet 2 in the first place as far as I can see in the file I downloaded.
 

thesmallfry123

New Member
Joined
Jul 16, 2020
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
thank you all for the help. I have managed to perform the task successfully. Thanks once again for the prompt aid.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,365
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for letting us know. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,126,997
Messages
5,622,083
Members
415,875
Latest member
Tarali

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