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!

On Apr 5, 1:21*am, wreth...@gmail.com 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.

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 <ica...@gmail.com> wrote:
> On Apr 5, 1:21 am, wreth...@gmail.com 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.

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 <inciden...@hotmail.co.uk> 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

On Apr 5, 2:37*am, Incidental <inciden...@hotmail.co.uk> 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.

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 <inciden...@hotmail.co.uk> 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

On Apr 5, 3:11*am, wreth...@gmail.com 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 <inciden...@hotmail.co.uk> 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.