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.