Deleting multiple rows of specific data

[ufeeboy]

Hi everyone,

I need to create a formula that will find and delete entire rows of data in sheet 1 based on the data in sheet 2. The data I want to delete in sheet 1 is identical to what's in sheet 3 but a subset of it. I have about 10000 rows of data to search through so I'd rather not do it one at a time. The formula would read the data in Column C (address) of sheet 2, search for it in sheet 1 and delete that whole row in sheet 1. Thanks in advance for your help!

[Richard Buttrey]

Hi and welcome to the forum.
This was confusing at first since you mentioned Sheet 1 & Sheet 2 but didn't identify which they are.

However this macro should do what you want

Please Login or Register  to view this content.

[ufeeboy]

Hi and welcome to the forum.
This was confusing at first since you mentioned Sheet 1 & Sheet 2 but didn't identify which they are.

However this macro should do what you want

Sorry it was hard for me to explain it haha. Thank you for taking the time to do this. I tried it but it deleted the data in the second sheet "results" and not the first one "50258 sample"
I need it to keep the data in the second sheet "results" and delete it from the first sheet "50258 sample"

[Richard Buttrey]

This is confusing.

In your OP your talked of deleting rows in Sheet1. When there is no sheet tab name "Sheet1" nor even a VBA code name Sheet1 then can you see how confusing it can be.

Your two sheets' tab names are "50258 sample" and "Result" It's reasonable to assume in the absence of you clearly defining sheet 1, that you meant it to be the first shown tab name going left to right, i.e. the "50258 sample" sheet. That sheet has a VBA code name "sample_sheet"

I interpreted the "Result" sheet is what you want the 50258 sheet to look AFTER the rows have been deleted from the 50258 Sample sheet. It's even more confusing since you mention a Sheet 3 which doesn't exist.

Hence my VBA code deletes the rows in the 50258 Sample sheet. See the With block of code

Please Login or Register  to view this content.

where column G has been added and identifies which values on the 50258 sheet don't exist.

All that said you appear to be saying that the rows that are currently being deleted should remain and the others remain. In which case just change the first line of code in the With block to

.Range("G2:G" & lLastRow) = "=IF(MATCH(C2,Result!C:C,FALSE)>0,""Delete"")"

[ufeeboy]

where column G has been added and identifies which values on the 50258 sheet don't exist.

All that said you appear to be saying that the rows that are currently being deleted should remain and the others remain. In which case just change the first line of code in the With block to

.Range("G2:G" & lLastRow) = "=IF(MATCH(C2,Result!C:C,FALSE)>0,""Delete"")"

Now I'm confused too. I created a Loom video that may help. This is my first time working with macros so not even sure if I'm running them correctly. If you want I can Venmo you some money to get this working.

I can't post the link to my loom video yet.

[Richard Buttrey]

In the file you have sent none of the 800 addresses in column C on the 50258 Sample sheet exist in the results sheet. Hence there are no records to delete.

Please update an example workbook which has three sheets.

Sheet1. A sheet which contains your original data
Sheet 2 A sheet which shows a subset of the SHeet 1 Data
Sheet 3 What you expect Sheet 1 to look like after the relevant records have been deleted.

[ufeeboy]

Here it is. I picked a continuous set of rows (100-119) but in reality the addresses would be spread out throughout Sheet1.