+ Reply to Thread
Results 1 to 16 of 16

Delete names from a list

  1. #1
    ClemCadidlhoper
    Guest

    Delete names from a list

    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.

  2. #2
    Don Guillett
    Guest

    Re: Delete names from a list

    Look in vba help for FINDNEXT. There is a good example

    --
    Don Guillett
    SalesAid Software
    [email protected]
    "ClemCadidlhoper" <[email protected]> wrote in
    message news:[email protected]...
    > 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.




  3. #3
    Dave Peterson
    Guest

    Re: Delete names from a list

    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

  4. #4
    ClemCadidlhoper
    Guest

    Re: Delete names from a list

    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
    >


  5. #5
    ClemCadidlhoper
    Guest

    Re: Delete names from a list

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


  6. #6
    Dave Peterson
    Guest

    Re: Delete names from a list

    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

  7. #7
    ClemCadidlhoper
    Guest

    Re: Delete names from a list



    "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
    >


  8. #8
    ClemCadidlhoper
    Guest

    Re: Delete names from a list

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



    "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
    >


  9. #9
    ClemCadidlhoper
    Guest

    Re: Delete names from a list

    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
    >


  10. #10
    ClemCadidlhoper
    Guest

    Re: Delete names from a list

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

    "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
    > >


  11. #11
    Dave Peterson
    Guest

    Re: Delete names from a list

    Nope.

    ClemCadidlhoper wrote:
    >
    > If the two worksheets are formatted in different fonts, will that make the
    > program not run?
    >
    > "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

  12. #12
    Dave Peterson
    Guest

    Re: Delete names from a list

    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.

    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

  13. #13
    ClemCadidlhoper
    Guest

    Re: Delete names from a list

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


  14. #14
    Dave Peterson
    Guest

    Re: Delete names from a list

    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

  15. #15
    ClemCadidlhoper
    Guest

    Re: Delete names from a list

    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

    "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
    >


  16. #16
    Dave Peterson
    Guest

    Re: Delete names from a list

    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.

    ClemCadidlhoper wrote:
    >
    > 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
    >
    > "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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1