If it's deleting fewer than you expect, I would think that it's because there is
an actual difference between the cells that you think are the same.

Maybe there are leading/trailing/extra embedded spaces. Maybe it's a spelling
difference.

If you could pick out the cells that should match (by just eyeballing it), you
could use a formula:

=sheet2!Axx=sheet1!Ayy
=sheet2!Bxx=Sheet1!Byy

My bet is one of those values will be false.



ClemCadidlhoper wrote:

First let me appologize for the multiple posts. This time I have a question
that I have worked on for a while. It seems the program works, but it skips
around and deletes about 1/2 of the rows I thought it might be because "sheet
2" comes to me with some of the names in red and others in black. but that
doesn't explain it. It deleted the first one then skipped the next 3, deleted
3, skipped 5, deleted 3, skipped 1, deleted 3, skipped 2,.....etc......a
couple places it skipped one and deleted one and skipped the next. I have
tried looking thru the code and cannot see anything obvious to me, that would
explain it. Any ideas?

"ClemCadidlhoper" wrote:

What does this mean? "Object doesn't support this property or method."?

"Dave Peterson" wrote:

I don't think I'd use an event to run this.

I'd run it on demand--after I do all the updating.

I'm not sure what you mean about getting it to work, but...

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



ClemCadidlhoper wrote:

It looks good, but I questions. I guess I don't know how to make it work.
Sheet 1 has about 400 names. Monthly, names are added to sheet 2. I think I
need an event procedure to tell it to run when data is added to sheet 2.

"ClemCadidlhoper" wrote:

I appreciate your response. Now I hope it works

"Dave Peterson" wrote:

Try this.

Option Explicit
Sub testme()

Dim MstrWks As Worksheet
Dim SecondWks As Worksheet
Dim myCell As Range
Dim MstrRng As Range
Dim SecondRng As Range
Dim DelRng As Range
Dim res As Variant
Dim myFormula As String

Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1")
Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2")

With MstrWks
Set MstrRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With SecondWks
Set SecondRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In MstrRng.Cells
myFormula = "Match(1,(" & myCell.Address(external:=True) _
& "=" & SecondRng.Address(external:=True) & ")*(" _
& myCell.Offset(0, 1).Address(external:=True) _
& "=" & SecondRng.Offset(0, 1).Address(external:=True) _
& "),0)"
res = Application.Evaluate(myFormula)

If IsError(res) Then
'no match, do nothing
Else
If DelRng Is Nothing Then
Set DelRng = myCell
Else
Set DelRng = Union(myCell, DelRng)
End If
End If
Next myCell

If DelRng Is Nothing Then
'do nothing
Else
Application.Goto DelRng.EntireRow
'DelRng.EntireRow.Delete
End If

End Sub

Change the worksheet names and workbook names in these lines:
Set MstrWks = Workbooks("book1.xls").Worksheets("sheet1")
Set SecondWks = Workbooks("book1.xls").Worksheets("sheet2")

And I do a select the range instead of deleting it--nice for testing:

Application.Goto DelRng.EntireRow
'DelRng.EntireRow.Delete

Delete the .goto line and uncomment the next line after you've verified that it
works ok.

ClemCadidlhoper wrote:

I need to open a master name list and delete rows from it based on the
content of another worksheets. More specifically I need to have it look at
the info in columns A and B and if that same information is in columns A and
B of the second document, then delete the whole row from the first document.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson