Delete names from a list

C

ClemCadidlhoper

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.
 
D

Dave Peterson

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.
 
C

ClemCadidlhoper

I appreciate your response. Now I hope it works

Dave Peterson said:
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.
Click to expand...
 
C

ClemCadidlhoper

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.
 
C

ClemCadidlhoper

If the two worksheets are formatted in different fonts, will that make the
program not run?
 
C

ClemCadidlhoper

Please disregard the last post. I figured this out already.

ClemCadidlhoper said:
What does this mean? "Object doesn't support this property or method."?
Click to expand...
 
D

Dave Peterson

It means that the code is trying to do something that can't be done.

You may want to be a little more specific--what line causes the error?

In fact, you may want to post your current code.
What does this mean? "Object doesn't support this property or method."?
Click to expand...
 
C

ClemCadidlhoper

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 said:
What does this mean? "Object doesn't support this property or method."?
Click to expand...
 
D

Dave Peterson

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.
 
C

ClemCadidlhoper

Dave, Could you explain the following code. I am just not getting the
programming from all of the places I have looked for instruction, but it
seems that there is something in this code or not in this code, which is
keeping this from working the way I need it to.

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
 
D

Dave Peterson

The match is equivalent to this in a worksheet:

=match(1,(c2=c3:c88)*(d2=d3:d88),0)

(well, depending on the addresses of those variables.)

The c2=C3:c88 return an array of trues and falses.
the d2=d3:d88 return an array of trues and falses.

When you multiply the trues and falses together, you get another array of
1/0's. But to get a 1, both conditions had to be true.

The =match(1,thatarrayof1/0,0)
says to find the first 1 in that array--the first time both column C and D were
equal to C2 and D2 on the same row.

If it's an error, then there was no match.

If the match returns a number, then there was a match.

I don't know if this does what you want, though.
 
Top