deleting rows if they contain a value held in a separate list

[wrethams]

I have a list of codes in column A

I want to delete any rows from column A if they contain any codes that
appear in a list held on a separate tab.

Any help appreciated!

 

[Ivyleaf]

I have a list of codes in column A

I want to delete any rows from column A if they contain any codes that
appear in a list held on a separate tab.

Any help appreciated!

Hi,

You could do this with a macro or formulas and a set of steps. Which
is best probably depends on how often you need to do it.

To do it manually, use the following process (assuming your List on
sheet 1 spans Cols A:G and your list on sheet 2 is in the range A1:A50
- just for example).

1. In cell H2 of sheet 1 (next to your list) put the
formula: "=ISERROR(MATCH(A2,Sheet2!$A$1:$A$50,0))"
2. Fill down to the end of your list
3. Turn on Autofilter
4. In col H filter for 'False'
5. Select all the rows and delete them
6 Turn off Autofilter.

If this isn't appropriate, a macro can easily be concieved.

Cheers,
Ivan.

 

[wrethams]

Thanks Ivan - it successfully identifies the records just how I want.

However I get a problem when I try to delete - Excel just hangs,
probably due to the # of entries (10,000 or so)

Can I macro get round this?

 

[wrethams]

Steve - sorry for being thick here

My column A that I want to delete the rows from is on sheet2. The list
of codes I'm cross checking against is on sheet3.

How do I tweak the code to reflect this?

 

[Ivyleaf]

Hi

All you need to do is change the following line to reflect which sheet
you are running the "FIND" on like so

Set FoundCell = Worksheets("Sheet2").Cells _
.Find(What:=MyCell, LookAt:=xlWhole)

The code already takes the list to search for from sheet3.  i hope
this makes it a little clearer for you but if you have any more
problems with the code let me know and i will comment it for you.

Steve

Hi,

Just a word of caution,

If you are looping through the cells to find the matches and delete,
that's fine but you won't be able to use a For Each... Next loop since
this will start from the top. If you are going to be deleting rows,
you need to start from the bottom and work up. Reason for this is that
say if your macro identifies row 10 for deletion, it will delete it
and then look at row 11 next. However, because you deleted row 10, row
11 is now row 12 if that makes sense.

Cheers,
Ivan.

 

[wrethams]

Hi Steve

No idea what I'm screwing up here! Just to be clear, the values I want
to delete are on sheet2 column J

the script looks like:

Option Explicit
Dim MyCell, MyRng As Range
Dim FoundCell As Range
Dim LastRow As Integer
Private Sub CommandButton1_Click()

Worksheets("Sheet3").Activate

LastRow = [A65535].End(xlUp).Row

Set MyRng = Range("J1:J" & LastRow)

For Each MyCell In MyRng

Set FoundCell = Worksheets("Sheet2").Cells _
..Find(What:=MyCell, LookAt:=xlWhole)

If Not FoundCell Is Nothing Then

FoundCell.EntireRow.Delete

End If

Next MyCell

End Sub

when I run it I get a subscript out of range error.

 

[Ivyleaf]

Hi Steve

No idea what I'm screwing up here! Just to be clear, the values I want
to delete are on sheet2 column J

the script looks like:

Option Explicit
Dim MyCell, MyRng As Range
Dim FoundCell As Range
Dim LastRow As Integer
Private Sub CommandButton1_Click()

Worksheets("Sheet3").Activate

LastRow = [A65535].End(xlUp).Row

Set MyRng = Range("J1:J" & LastRow)

For Each MyCell In MyRng

Set FoundCell = Worksheets("Sheet2").Cells _
.Find(What:=MyCell, LookAt:=xlWhole)

If Not FoundCell Is Nothing Then

    FoundCell.EntireRow.Delete

End If

Next MyCell

End Sub

when I run it I get a subscript out of range error.

Hi

All you need to do is change the following line to reflect which sheet
you are running the "FIND" on like so

Set FoundCell = Worksheets("Sheet2").Cells _
.Find(What:=MyCell, LookAt:=xlWhole)

The code already takes the list to search for from sheet3.  i hope
this makes it a little clearer for you but if you have any more
problems with the code let me know and i will comment it for you.

Steve- Hide quoted text -

- Show quoted text -

Hi,

Give this a try:

Option Explicit
Private Sub CommandButton1_Click()
Dim ChkList As Range, DelRange As Range
Dim LastRw As Long, i As Long

LastRw = Sheets("Sheet3").Range("J65535") _
.End(xlUp).Row
Set ChkList = Sheets("Sheet3").Range("J1") _
.Resize(LastRw, 1)

LastRw = Sheets("Sheet2").Range("A65535") _
.End(xlUp).Row
Set DelRange = Sheets("Sheet2").Range("A1") _
.Resize(LastRw, 1)

For i = LastRw To 1 Step -1
If Not IsError(Application.Match _
(DelRange.Cells(i), ChkList, 0)) Then
DelRange.Cells(i).EntireRow.Delete
End If
Next
End Sub

This will work from the bottom up like I suggested and should do what
you need. This assumes that your data which you wish to delete your
rows from is on "Sheet2" in Column "A", and that your list of values
that you are checking for is on "Sheet3" in Column "J". If I got that
wring, just modify the top couple of lines.

Cheers,
Ivan.