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

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!

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

On Apr 5, 1:21*am, wrote:
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.

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

Hi Wreth

One way to do it in code would be

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("A1:A" & LastRow)

For Each MyCell In MyRng

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

If Not FoundCell Is Nothing Then

FoundCell.EntireRow.Delete

End If

Next MyCell

End Sub

I hope this helps you out.

Steve

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

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?

On Apr 4, 3:40 pm, Ivyleaf wrote:
On Apr 5, 1:21 am, wrote:

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.

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

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?

On Apr 4, 3:53 pm, Incidental wrote:
Hi Wreth

One way to do it in code would be

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("A1:A" & LastRow)

For Each MyCell In MyRng

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

If Not FoundCell Is Nothing Then

FoundCell.EntireRow.Delete

End If

Next MyCell

End Sub

I hope this helps you out.

Steve

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

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

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

On Apr 5, 2:37*am, Incidental wrote:
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.

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

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.


On Apr 4, 4:37 pm, Incidental wrote:
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

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

On Apr 5, 3:11*am, wrote:
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.

On Apr 4, 4:37 pm, Incidental wrote:



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.

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

Hi

The problem you are having with my code is that you need to change
LastRow = [A65535].End(xlUp).Row to find the last value in your
choosen column which is J which would be LastRow =
[J65535].End(xlUp).Row. That said Ivan is right working your way down
and deleting rows can cause problems so i would suggest going with his
loop for the removal of the rows. I should have thought of that one
D'oh!!!! Thanks for the heads up and i hope you got sorted out.

Steve