Delete rows based on list

**drchris** · 10-19-2009, 07:15 AM

Guys, I have a simple VBA question but I've been looking around and I can't crack it!

I'd like to delete rows based on a list. So:

I have a tab with a big list of data:
A B C D
--------
XX AA 3 4
YY BB 5 8
ZZ CC 6 9
...

Now I'd like to delete rows based on another list on another tab. This list could expand in the future.

So the other tab (delete) list:
A C
----
XX 3
ZZ 6

So the macro would check A&C from delete list = A&C from original list and delete the row. So the result on first tab would be:

A B C D
--------
YY BB 5 8

Thank you so much.
DrChris

**Richard Buttrey** · 10-19-2009, 07:43 AM

Hi,

Assumptions.
Data is on sheet1 A1:D4 with ABCD as column labels on row 1
The delete list is on sheet2 in A1:C3 with labels on row 1.

First create two range names
Data
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),4)
DeleteCriteria
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),2)

Then add the following macro

Please Login or Register  to view this content.

HTH

**drchris** · 10-19-2009, 09:32 AM

Thank u for your help
Chris

**drchris** · 10-19-2009, 09:43 AM

Rich it doesnt work.

After I run the sub, it hides cells 2-4
And I get a run time error: No cells were found.

Thank u
DrChris

**drchris** · 10-19-2009, 10:19 AM

Book is attached. I would really appreciate someones help as its for work and my boss is getting on my nerves to get it to work.
Thanks
Chris

**Richard Buttrey** · 10-19-2009, 10:22 AM

Hi,

See the attached. It works in this book.
(I've changed the last instruction from a filter to a showall, but that would not have caused the problem you flagged up).

Regards

**drchris** · 10-19-2009, 10:29 AM

You're right. It works fine now, so very appreciated for that Rich.
I need to adjust it to work on my work spreadsheet now.

So instead of looking at columns 1 and 3, it needs to look at 4 & 6. And there are 14 columns in sheet1 not just 4.

Ive changed this to 14
Data
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),14)

But also the code to 14

Sub DeleteRows()
Range("Data").AdvancedFilter action:=xlFilterInPlace, criteriarange:=Range("deletecriteria")
Range("Data").Offset(1, 0).Resize(Range("Data").Rows.Count - 1, 14).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Sheet1.ShowAllData
End Sub

But no luck. Any ideas?

Thank you
C